DML Commands in SQL Server 2008

In this article I describe about various DML commands in SQL Server.
  • 2536

DML( Data Manipulation Language)

DML commands are editing commands which are used for updating, deleting, querying and inserting data in database. Example: Select, Insert, Update and Delete statements.

First of all create a table

CREATE TABLE BOOKS

(

Id int,

Name nvarchar(50),

Author nvarchar(50),

Price decimal(18,2)

)

GO

select * from BOOKS

BOOKS table in sql.jpg

1. Insert

Insert command is used to insert a new row in a table or a view. We can insert one or more rows using Insert command. While inserting record , the number of inserted records should match the number of columns of the table and its data type too. If number of entered records are less than number of columns then the field names must be entered with the insert statement.

Example

Insert single row

INSERT INTO BOOKS

VALUES (1,'ASP.NET 3.5 UNLEASHED','Stephen Walther',600)

GO

select * from BOOKS

Output:

insert single row in table.jpg

Insert multiple rows

INSERT INTO BOOKS

select 2,'DATA STRUCTURES', 'SEYMOUR LIPSCHUTZ',300 union all

select 3,'jQuery UI','Eric Sarrion',200

 

GO

select * from BOOKS

Output:

insert multiple rows.jpg

Insert values for some columns

INSERT INTO BOOKS(Id,Name,Price)

values(4,'HTML5',250) 

GO

select * from BOOKS

Output:

insert value of some columns.jpg

2. Update

Update is used to change information contained in table. We can update single record or set of records or all records using Update command. Where clause is used to specify condition on which record is deleted.

Example:

update BOOKS set Price=450 where Name='HTML5'

GO

select * from BOOKS

Output:

update records in sql.jpg

3. Delete

Delete command is used to delete one or more record from a table. Condition can be given in Where clause to specify which row to remove.

Example:

delete from BOOKS where Author is NULL

Go

select * from BOOKS

Output:

delete from table insql.jpg

delete from BOOKS where Price<400

Go

select * from BOOKS

Output:

insert single row in table.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.