I often have to insert multiple rows of (usually) test data into tables. The simplest but most tedious way is to rewrite the INSERT statement over and over with different values …

INSERT INTO User VALUES ('Penny', 'Penny')
INSERT INTO User VALUES ('Sheldon', 'Cooper')
INSERT INTO User VALUES ('Leonard', 'Hofstadter')
INSERT INTO User VALUES ('Howard', 'Walowitz'
INSERT INTO User VALUES ('Rajesh', 'Koothrappali')


Its not a big deal but as a developer I hate writing the same statement over and over … it just goes against my grain. Its also more of an overhead to repeatedly call insert for each row. Here’s a better way …

INSERT INTO User
SELECT 'Penny', 'Penny' UNION ALL
SELECT 'Sheldon', 'Cooper' UNION ALL
SELECT 'Leonard', 'Hofstadter' UNION ALL
SELECT 'Howard', 'Walowitz' UNION ALL 
SELECT 'Rajesh', 'Koothrappali'



This inserts the same data as the first example but uses only one insert so has less overhead. If you happen to be running SQL Server 2008 there’s a new syntax to achieve the same although the above example will still work. In SQL Server 2008 you can use this syntax …

INSERT INTO User
VALUES ('Penny', 'Penny')
, ('Sheldon', 'Cooper')
, ('Leonard', 'Hofstadter')
, ('Howard', 'Walowitz') 
, ('Rajesh', 'Koothrappali')