SQL Server Brain Basher of the Week #018 - Single insert with multiple rows

Here is a basic concept you should know. It is about inserting records, specifically inserting multiple records with a single insert statement. 

Can we insert multiple rows using a single insert statement?

Yes, this is possible, however code has to be constructed as per the version of SQL Server. SQL Server 2008 started supporting an enhanced VALUES clause that allows us to submit multiple rows separated by comma. If the version is lower than 2008, UNION ALL has to be used for constructing the code that helps us to build a virtual table on values.

Note that both these operations are processed as an atomic operation, hence a failure on one row fails the whole operation, inserting none of the records into the table.

Here is a sample code.

view plainprint?

1. -- Connecting with the temp table  
2. USE tempdb;  
3. GO  
4.   
5. -- Creating a test table  
6. CREATE TABLE dbo.TestTable  
7. (  
8.  Id int PRIMARY KEY  
9.  , Value varchar(20) NOT NULL  
10. );  
11. GO  
12.   
13. -- Inserting multiple rows with enhanced VALUES clause  
14. -- This is possible only with SQL Server 2008 or later  
15. INSERT INTO dbo.TestTable  
16. VALUES  
17. (1, 'abc'),  
18. (2, 'abc'),  
19. (3, 'abc')  
20.   
21. -- Inserting multiple rows with UNION ALL  
22. -- This is what we have used with older version  
23. INSERT INTO dbo.TestTable  
24. SELECT 4, 'abc' UNION ALL  
25. SELECT 5, 'abc' UNION ALL  
26. SELECT 6, 'abc';  
27.   
28.   
29. SELECT * FROM dbo.TestTable;  

-- Connecting with the temp table
USE tempdb;
GO

-- Creating a test table
CREATE TABLE dbo.TestTable
(
 Id int PRIMARY KEY
 , Value varchar(20) NOT NULL
);
GO

-- Inserting multiple rows with enhanced VALUES clause
-- This is possible only with SQL Server 2008 or later
INSERT INTO dbo.TestTable
VALUES
(1, 'abc'),
(2, 'abc'),
(3, 'abc')

-- Inserting multiple rows with UNION ALL
-- This is what we have used with older version
INSERT INTO dbo.TestTable
SELECT 4, 'abc' UNION ALL
SELECT 5, 'abc' UNION ALL
SELECT 6, 'abc';


SELECT * FROM dbo.TestTable;