SQL Server Portal

Today, we will discuss the multiple ways to insert multiple records in the table using one insert statement. We have different ways to achieve it but there are pros and cons in each method that we will discuss later in this article. Lets create a table to demonstrate how it works.

Create Table Test_Table
(
[ID] int,
[Designation] varchar(50)
)

Let me explain this with different methods and simple examples.

Method -1 : Traditional Insert statement (Multiple records with Multiple Statements)

In this Method, we will discuss the usual method to insert multiple records with multiple statements. However, if you need to insert multiple records, you need to write multiple statements. And it is a little bit time consuming as well because you need to write statement on each line.

Insert into Test_Table ([ID],[Designation]) Values(1,'Officer')
Insert into Test_Table ([ID],[Designation]) Values(2,'Manager')
Insert into Test_Table ([ID],[Designation]) Values(3,'Director')

Method-2 : Insert statement – Table Value Constructor – (Multiple records with Single Statement)

In SQL server 2008, a solution came for multiple records insertion with a single statement namely “Table Value Constructor”.  Lets make an example and check how it works.

Insert into Test_Table Values (1,'Officer'),(2,'Manager'),(3,'Director')

Note : We can construct maximum 1000 rows using this method in a single statement. If it exceeds 1000, it will generate an error message.

Method-3 : Insert statement – Union ALL – (Multiple records with Single statement
In this example, we will insert multiple records with a single statement using “Union ALL”.

Insert into Test_Table
Select 1,'Officer'
UNION ALL
Select 2,'Manager'
UNION ALL
Select 3,'Director'

Note : In this method, the performance is inversely proportional to the length of the statement, ie., longer the UNION ALL statement grows, lesser the performance will be.

Conclusion : I discussed various ways to insert multiple records using single statement but before applying any method we need to review the requirement and should keep the pros and cons in mind.