Using DML to Modify Data

The estimated reading time for this post is 9 minutes

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:


INSERT [INTO] <Table or View> [(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.


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.

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:


INSERT [INTO] <table or view> [(column_list)] 
SELECT <column_list> FROM <table_list>...;

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:


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.


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:


UPDATE <TableName> 
<ColumnName1> = { expression | DEFAULT | NULL }       

UPDATE Example

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

MERGE INTO schema_name.table_name	AS TargetTbl     
USING (SELECT <select_list>)	AS SourceTbl
ON (TargetTbl.col1 = SourceTbl.col1)
UPDATE SET TargetTbl.col2 = SourceTbl.col2
	INSERT (<column_list>) 
	VALUES (<value_list>);

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

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.


DELETE [FROM] <Table or VIEW > 
[ WHERE { <search_condition>, …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

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:



The following example removes all data from the JobCandidate table.


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

IDENTITY [ (seed , increment) ]

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

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:


CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { 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.


CREATE SEQUENCE dbo.demoSequence
CREATE TABLE dbo.tblDemo
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.

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2008. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website