Bulk insert of rows with sql server

I'm not sure how many total weeks of my life I've spent on data import. Many. Maybe Hundreds. Also, every new project needs test data and usually we'll write INSERT INTO blah blah full scripts again and again. Here's a way to slightly reduce the amount of text that needs to be written/sent to the server AND make your scripts cleaner. Available Sql Server 2008 and up.
December 07 2012

If you're a developer who started their trade before the ORM days, you've likely had to insert data en masse into a sql server db , if for only test purposes.  Usually this involves writting out INSERT INTO statements, one complete DDL command for each row.  Seems rather crazy to repeat the columns you want to insert data into.  There was a sort-of workaround where you could use SELECT and UNION ALL to put them together.

INSERT INTO [Region] ([Id], [Code],[Description],[SortOrder]) VALUES (1, 'LS', 'Lumbar Spine', 1)
INSERT INTO [Region] ([Id], [Code],[Description],[SortOrder]) VALUES (2, 'NH', 'Neck & Head', 2)
INSERT INTO [Region] ([Id], [Code],[Description],[SortOrder]) VALUES (3, 'KN', 'Knee', 3)
INSERT INTO [Region] ([Id], [Code],[Description],[SortOrder]) 
SELECT 1, 'LS', 'Lumbar Spine', 1
UNION ALL SELECT 2, 'NH', 'Neck & Head', 2
UNION ALL SELECT 3, 'KN', 'Knee', 3


Since Sql Server 2008 (!) there has been a concept called Row Constructors which allows Sql Server developers to avoid this verbose syntax.

INSERT INTO [Region] ([Id], [Code],[Description],[SortOrder]) 
    (1, 'LS', 'Lumbar Spine', 1),
    (2, 'NH', 'Neck & Head', 2),
    (3, 'KN', 'Knee', 3)


A whole bunch easier to see and saves the extra typing. If only I wasn’t 4 years late to the party…

Post a comment

comments powered by Disqus