Inserting multiple rows in single INSERT

Do you know there is a new T-SQL command in SQL Server 2008 which facilitates you to insert several rows in single INSERT statement? This is really a cool feature to me. As a DBA I want to write many data patches to populate data specially for lookup data. Let me say, you want to insert several records into a table. You will have to write several INSERT statements to accomplish this task in prior versions of SQL Server. Now in SQL Server 2008 it is very easy. So I thought of posting this blog for your reference. Read the full blog to see this cool command. 


I want to write a data patch to populate the table, dbo.CountryRegion.



IF OBJECT_ID('dbo.CountryRegion','U') IS NOT NULL
DROP TABLE dbo.CountryRegion

CREATE TABLE dbo.CountryRegion
(
CountryRegionCode char(2)
,[Name] varchar(100)
)





For illustration purposes I will take only five records as below;

CountryRegionCode	[Name]
AD	Andorra
AU	Australia
CU	Cuba
BR	Brazil
LK	Sri Lanka

If you use traditional INSERT method you will end up writing five individual INSERT statements which is a bit tedious task if the no.of records are high.


VALUES Clause
In SQL Server 2008 you can accomplish the same task by using the code portion mentioned below;



--New method in SQL Server 2008
INSERT dbo.CountryRegion (CountryRegionCode,[Name])
VALUES 
  ('AD','Andorra')
 ,('AU','Australia')
 ,('CU','Cuba')
 ,('BR','Brazil')
 ,('LK','Sri Lanka')
 
But if you think little bit further you may accomplish the same thing in SQL Server 2005 as well. Try this code.

--SQL Server 2005 and prior versions
INSERT dbo.CountryRegion (CountryRegionCode, )
SELECT 'AD','Andorra'
UNION ALL
SELECT 'AU','Australia'
UNION ALL
SELECT 'CU','Cuba'
UNION ALL
SELECT 'BR','Brazil'
UNION ALL
SELECT 'LK','Sri Lanka'

Only difference from the code syntax perspective is more commands in the second method.(e.g: SELECT, UNION [ALL])



The advantage of this single statement is, the transaction atomicity.  If the statement fails it will not insert any record. I did a performance comparison of these two methods and the Execution Plan is the same in both methods. Which means even the VALUES clause in SQL Server 2008 is handling internally like the same way of using multiple SELECT and UNION ALL statements. 

However it is easy to code this in SQL Server 2008 than prior versions. I expect much improved version of this method in SQL Server 2010.