DML Commands in SQL Server 2008
In this article I describe about various DML commands in SQL Server.
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
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 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 values for some columns
INSERT INTO BOOKS(Id,Name,Price)
values(4,'HTML5',250)
GO
select * from BOOKS
Output:
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:
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 BOOKS where Price<400
Go
select * from BOOKS
Output: