Insert Multiple rows with a Single INSERT Statement using SQL Server Table Value Constructor

sql server

← PrevNext →

Tools you can use

While managing an Inventory management System for an organization, I have came across a situation where I had to perform bulk upload on a table in SQL Server. Bulk upload requires inserting multiple rows of data in a table.

The application that was required to extract data was on the cloud. Hence, writing INSERT statements repetitively for individual rows were not a good idea. I needed a better solution to insert several rows together, without losing a data in the process.

The solution came in the form of Table Value Constructor, a feature introduced in SQL Server 2008. This feature has many uses. However, in this article we will see how TVC is useful for inserting several rows in a table using a single statement.

Losing data while extracting or storing in a database is a serious headache and no DBA or Database programmer would like to be in this situation. Therefore, once we have extracted and received the data at the server, we would like to insert the data in a table quickly and efficiently without any fuss.

A quick and efficient way of doing this is to write a single INSERT statement to insert multiple rows in a table in SQL Server.

If you are a .net programmer and wish to do bulk upload on a database table using Asp.Net, then I recommend you check this article on uploading bulk of data using SqlBulkCopy class in Asp.Net. This article has code written in C# as well as Vb.Net. SqlBulkCopy is a faster and efficient solution to upload large data into an SQL Server table.

INSERT Statement using TVC (Table Value Constructor)

Introduced in SQL Server 2008 (supported in current versions like 2014), we can define TVC in our DML statements. A TVC is a set of data arranged in a tabular formatT. We will use TVC as the VALUES clause in our INSERT statement.

“I recommend using the SQL Server dummy database with tables and few dummy data in it. I have designed especially for moments like this.”

CREATE TABLE dbo.Employee
    (EmpID INT NOT NULL , 
        EmpName VARCHAR(50) NOT NULL, 
	    Designation VARCHAR(50) NULL
	    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (EmpID)
    )
INSERT INTO dbo.Employee 
    (EmpID, EmpName, Designation)
VALUES
    (1, 'ARUN BANIK', 'PROGRAMMER'),
    (2, 'MIKE PEARL', 'SENIOR ACCOUNTANT'),
    (3, 'GREEN FIELD', 'ACCOUNTANT'),
    (4, 'DEWANE PAUL', 'PROGRAMMER'),
    (5, 'MATTS', 'SR. PROGRAMMER'),
    (6, 'PLANK OTO', 'ACCOUNTANT')
	
SELECT *FROM dbo.Employee

A careful look at it and you will see it has similarities with previously used INSERT statement using VALUES clause. Developers using SQL Server versions prior to 2008 would have used multiple SELECT OR VALUES clause along with the INSERT statement.

Using SELECT clause with INSERT Statement

INSERT INTO Employee (EmpID, EmpName, Designation)
    SELECT 1, 'ARUN BANIK', 'PROGRAMMER'
INSERT INTO Employee (EmpID, EmpName, Designation)
    SELECT 2, 'MIKE PEARL', 'SENIOR ACCOUNTANT'

Using VALUES clause with INSERT Statement

INSERT INTO Employee (EmpID, EmpName, Designation)
	VALUES (1, 'ARUN BANIK', 'PROGRAMMER')
INSERT INTO Employee (EmpID, EmpName, Designation)
	VALUES (2, 'MIKE PEARL', 'SENIOR ACCOUNTANT')

With the TVC format, you do not have to write the VALUES clause multiple times, instead we write the VALUES clause once for each INSERT statement, and pass row of data inside the brackets.

Conclusion

Simplicity? Yes it is simple, since you can perform multiple INSERT using a single statement. It does not effect the performance at all. However, with TVC you can INSERT a set of 1000 rows at a time. Exceed the limit and SQL Server will throw an error.

← PreviousNext →