:::: MENU ::::

Insert multiple rows by using a single T-SQL insert statement

  • Nov 15 / 2008
  • 0
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Insert multiple rows by using a single T-SQL insert statement

Until SQL Server 2000 T-SQL, we have to use multiple insert statements for inserting multiple rows of data in a table. For SQL Server 2005 T-SQL we can insert multiple rows by using a single insert statement. And for SQL Server 2008 T-SQL we have two methods to insert multiple rows by using a single insert statement.
In following example we will go through the statements used in SQL Server 200 T-SQL, SQL Server 2005 T-SQL, SQL Server 2008 T-SQL to insert multiple rows by using single insert statement.

 --For SQL server 2000 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('SecondValue',2)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('ThirdValue',3)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FourthValue',4)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FifthValue',5)  
 GO  


 -- For SQL Server 2005 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 SELECT 'FirstValue' ,1  
 UNION ALL  
 SELECT 'SecondValue' ,2  
 UNION ALL  
 SELECT 'ThirdValue' ,3  
 UNION ALL  
 SELECT 'FourthValue' ,4  
 UNION ALL  
 SELECT 'FifthValue' ,5  
 GO  


 -- For SQL Server 2008 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1),  
 ('SecondValue',2),  
 ('ThirdValue',3),  
 ('FourthValue',4),  
 ('FifthValue',5)  
 GO  
Consult us to explore the Databases. Contact us