Hi dear all, hope you enjoy the last post about Sorting and Filtering Data. As you see below, transact-SQL (T-SQL) consists of several commands. One of the most important commands of T-SQL is DML. In this post we will learn about DML commands and how to use.

Transact-SQL (T-SQL) Data Manipulation Language (DML) is the subset of the SQL language that contains commands to add, modify and remove data values, within the rows or tables. In the following, we will discuss how to use INSERT to add new rows within tables, Update to make changes to rows within tables and DELETE to remove rows from tables. And we also learn some other DML commands such as TRUNCATE, IDENTITY and MERGE.

First, let’s take a look at the INSERT statement and how it works.

Using INSERT to Add Data

The INSERT statement is used to add one or more rows to a table and there are several forms of INSERT. The basic form of INSERT statement is as follows:


[codesyntax lang=”tsql”]

INSERT [INTO]  [(column_list)]  
-- column_list is optional but the code is safer with it 
VALUES ([Column Name or an expression or DEFAULT or NULL], …n)


By this form, called INSERT VALUES, you can specify the columns that will be filled with the values that will be presented for each row. If the column is not in column_list, the Database Engine will be able to provide a value based on the definition of the column; otherwise the row cannot be loaded.

Database Engine can process a value for a column automatically by one of these ways:

  • The column has an IDENTITY property. The next incremental value will be used.
  • The column has a default constraint. The default value will be used.
  • The column has a timestamp data type. The current timestamp value will be used.
  • The column is nullable. the NULL value will be used.
  • The column is a computed column. The calculated value will be used.


[codesyntax lang="tsql"]

INSERT INTO Sales.OrderDetails (OrderID, ProductID, UnitPrice, Qty, Discount)
 VALUES (10248, 39, 18, 2, 0.05)


As seen in INSERT syntax above column_list is optional segment; but if it is omitted, column values must be specified for each column, in order of the definition of columns in the table. In addition to inserting single row, the INSERT VALUES can also be used to insert multiple values at a time. The following example shows how to separate each row to insert multiple rows into a table concurrently.

[codesyntax lang="tsql"]

INSERT INTO Sales.OrderDetails (orderid, productid, unitprice, qty, discount)  
VALUES (10250,39,18,2,0.05) 
,             (10251,39,18,5,0.10) 
,             (10252,39,18,2,0.05)   
,             (10254,39,18,5,0.10);



Beyond specifying a literal set of values in an INSERT statement, you can also use the output of other operations for INSERT. Sometimes you need to add the result set of a SELECT statement to another table. The form, called INSERT SELECT, let you add the rows from SELECT clause to the destination table directly.

The following syntax display the INSERT SELECT statement:


[codesyntax lang="tsql"]

[(column_list)] SELECT FROM ...;


You may also need to add the result set of the stored procedure into a table. This form of INSERT, called INSERT EXEC, is the same as INSERT SELECT. The following example illustrate an INSERT EXEC statement:


[codesyntax lang="tsql"]

INSERT INTO Production.Products (productID, productname, supplierid, categoryid, unitprice) 
EXEC Production.AddNewProducts; 



In T-SQL, you can create and populate a new table by the results of a SELECT query. This form of INSERT, called SELECT INTO, just can be used for new table; this means that you cannot insert rows into existing table by using SELECT INTO. Each rows in the new table will have the same name, data type and nullability as the corresponding column in the SELECT list.


[codesyntax lang="tsql"]

SELECT	ordered, custid, empid, orderdate, shipcity, shipregion, shipcountry 
INTO	Sales.OrdersExport 
FROM Sales.Orders  WHERE  empid = 5;


After learning about INSERT and how it works, it is the time to learn how to write queries to modify rows by using UPDATE and perform a MERGE between source and target tables.

Using UPDATE to Modify Data

The UPDATE statement changes the existing data in a table or view. UPDATE operates on the set of rows with (or without) condition in a WHERE clause. To change the value, it uses a SET clause that can perform on one or more columns, separated by commas, to allocate new values. The basic syntax of UPDATE is as follow:


[codesyntax lang="tsql"]

 = { expression | DEFAULT | NULL }       


UPDATE Example

[codesyntax lang="tsql"]

UPDATE Production.Products       
SET unitprice = (unitprice * 1.04) 
WHERE categoryID = 1  
AND     discontinued = 0;


Using MERGE to Modify Data

One of the common needs in database is comparing two tables and doing some operations according to differences or similarities.

You can use MERGE statement to perform insert, update, or delete on a target table based on the results of a join with a source table. MERGE modifies data based on one or more of these conditions:

  • Update target data on matching the source data and the target data.
  • Delete target data on matching the source data and the target data.
  • Insert into target table when the source data has no match the data in target.
  • Delete from target table when the target data has no match the data in source.
  • Update target table when the target data has no match the data in source.

All operations in MERGE statements only effect on target table and source table will not be affected.

In the following, it shows each condition and the operation in accordance with it:

The following code illustrate the general syntax of a MERGE statement:

The Merge Syntax

[codesyntax lang="tsql"]

MERGE INTO schema_name.table_name	AS TargetTbl     
ON (TargetTbl.col1 = SourceTbl.col1)
UPDATE SET TargetTbl.col2 = SourceTbl.col2


The following example shows inserting new records from StoreBackup table to Store table:

[codesyntax lang="tsql"]

MERGE INTO Store AS Destination  
-- Known in online help as Target, which is a reserved word  
	USING StoreBackup AS StagingTable
	-- Known in online help as the source, which is also a reserved word  
	ON (Destination.BusinessEntityID = StagingTable.BusinessEntityID)
	-- the matching control columns 
		INSERT (BusinessEntityID, Name, SalesPersonID, Demographics, rowguid, ModifiedDate)
		VALUES (StagingTable.BusinessEntityID, StagingTable.Name, StagingTable.SalesPersonID, StagingTable.Demographics, StagingTable.rowguid, StagingTable.ModifiedDate);


Now let’s see how to remove data from tables.

Using DELETE to Remove Data

The DELETE statement removes the existing data from tables. DELETE can perform with (or without) condition in a WHERE clause. If there is no WHERE clause in DELETE statement, it will remove all records of the table.

the basic syntax of DELETE is as follow.


[codesyntax lang="tsql"]

[ WHERE { , …n}]


The following example deletes all rows from the ProductCostHistory table in which the value of the StandardCost column is more than 1000.

DELETE Example

[codesyntax lang="tsql"]

DELETE FROM Production.ProductCostHistory  
WHERE StandardCost > 1000.00;


Using TRUNCATE TABLE to Remove Data

TRUNCATE TALE is similar to DELETE statement with no WHERE clause. The TRUNCATE TABLE removes all rows from table without logging the individual rows deletion. TRUNCATE TABLE is faster and use fewer resources.

The following code shows the syntax of TRUNCATE TABLE:


[codesyntax lang="tsql"]



The following example removes all data from the JobCandidate table.


[codesyntax lang="tsql"]

TRUNCATE TABLE HumanResources.JobCandidate;


You may need to automatically generate sequential value for a column. SQL Server provide two ways to automatically generate sequential values: the IDENTITY property, and the SEQUENCE object. Both mechanism can be used to provide sequential number when new rows are inserted into the table.


The IDENTITY property is used in the CREATE TABLE and ALTER TABLE statement. it is defined on a column using exact-number or numeric with scale of 0 data types.

IDENTITY property has two optional arguments, seed (starting value) and increment (step value). If you leave out both, seed, and increment, will set to 1.

Only one column in each table can have IDENTITY property.

The following code shows the syntax of IDENTITY property

IDENTITY property syntax

[codesyntax lang="tsql"]

IDENTITY [ (seed , increment) ]


In the following example we create a table, called new_employees, which id_num column has an IDENTITY property.

[codesyntax lang="tsql"]

CREATE TABLE new_employees  
 id_num int IDENTITY(1,1),  
 fname varchar (20),  
 minit char(1),  
 lname varchar(30)  


When you want to insert into the table that has an IDENTITY column, INSERT statement should not reference the IDENTITY column. SQL Server will generate a value by next available value for the column. If the value must be explicitly allocated to an IDENTITY column, the SET IDENTITY INSERT statement must be executed to allow insertion to the IDENTITY column manually.

Each new value for the IDENTITY column is generated uniquely. However, the IDENTITY property (without primary key constraint or unique constraint) doesn’t guarantee uniqueness of the value.


As you learned, the IDENTITY property is used to generate a sequence for single column within a table. However, administrators and database developers may need coordinated values across multiple tables within a database.

SQL Server 2012 provide a new SEQUENCE object that can be referenced by multiple tables. The SEQUENCE object is an independent object that is created and modified by DDL statements such as CREATE, ALTER, and DROP. The SEQUENCE object definition has these arguments: data type (must be exact-number or numeric with scale of 0), starting value, increment value, minimum and maximum value to specify the bounds for SEQUENCE object, CYCLE (or NO CYCLE) to specify the SEQUENCE object should restart from the minimum value (or maximum value for descending SEQUENCE objects), and CACHE (or NO CACHE) to increase performance for application that use the SEQUENCE object frequently. The following code shows the create SEQUENCE syntax:


[codesyntax lang="tsql"]

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ { MINVALUE [  ] } | { NO MINVALUE } ]
[ { MAXVALUE [  ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [  ] } | { NO CACHE } ]
[ ; ]


The NEXT VALUE FOR function is used to retrieve the next available value from a sequence. The following example defines a SEQUENCE object and return the available value for the next INSERT statement.


[codesyntax lang="tsql"]

CREATE SEQUENCE dbo.demoSequence

CREATE TABLE dbo.tblDemo
(SeqCol int PRIMARY KEY,
ItemName nvarchar(25) NOT NULL);

INTO dbo.tblDemo (SeqCol,ItemName)
VALUES (NEXT VALUE FOR dbo.demoSequence, 'Item');


I hope this post was informative for you, please share it with others if you think it worth to read. Stay tuned to learn more about SQL Server.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles


Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788


Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan