Inserting multiple rows in a single SQL query

Question

I have multiple set of data to insert at once  say 4 rows  My table has three columns  Person  Id and Office   INSERT INTO MyTable VALUES   John   123   Lloyds Office    INSERT INTO MyTable VALUES   Jane   124   Lloyds Office    INSERT INTO MyTable VALUES   Billy   125   London Office    INSERT INTO MyTable VALUES   Miranda   126   Bristol Office      Can I insert all 4 rows in a single SQL statement

User · Answer

INSERT statements that use VALUES syntax can insert multiple rows  To do this  include multiple lists of column values  each enclosed within parentheses and separated by commas    Example   INSERT INTO tbl name  a b c  VALUES 1 2 3   4 5 6   7 8 9

User · Answer

If you are inserting into a single table  you can write your query like this  maybe only in MySQL    INSERT INTO table1  First  Last  VALUES       Fred    Smith          John    Smith          Michael    Smith          Robert    Smith

User · Answer

NOTE  This answer is for SQL Server 2005  For SQL Server 2008 and later  there are much better methods as seen in the other answers   You can use INSERT with SELECT UNION ALL   INSERT INTO MyTable   FirstCol  SecondCol      SELECT   First   1     UNION ALL SELECT   Second   2     UNION ALL SELECT   Third   3       Only for small datasets though  which should be fine for your 4 records

User · Answer

If you are inserting into a single table  you can write your query like this  maybe only in MySQL    INSERT INTO table1  First  Last  VALUES       Fred    Smith          John    Smith          Michael    Smith          Robert    Smith

User · Answer

If you are inserting into a single table  you can write your query like this  maybe only in MySQL    INSERT INTO table1  First  Last  VALUES       Fred    Smith          John    Smith          Michael    Smith          Robert    Smith

User · Answer

In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement   INSERT INTO MyTable   Column1  Column2   VALUES   Value1  Value2      Value1  Value2     For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008   For example   INSERT INTO MyTable     Column1  Column2  Column3   VALUES     John   123   Lloyds Office         Jane   124   Lloyds Office         Billy   125   London Office        Miranda   126   Bristol Office

User · Answer

If you are inserting into a single table  you can write your query like this  maybe only in MySQL    INSERT INTO table1  First  Last  VALUES       Fred    Smith          John    Smith          Michael    Smith          Robert    Smith

User · Answer

In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement   INSERT INTO MyTable   Column1  Column2   VALUES   Value1  Value2      Value1  Value2     For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008   For example   INSERT INTO MyTable     Column1  Column2  Column3   VALUES     John   123   Lloyds Office         Jane   124   Lloyds Office         Billy   125   London Office        Miranda   126   Bristol Office

User · Answer

NOTE  This answer is for SQL Server 2005  For SQL Server 2008 and later  there are much better methods as seen in the other answers   You can use INSERT with SELECT UNION ALL   INSERT INTO MyTable   FirstCol  SecondCol      SELECT   First   1     UNION ALL SELECT   Second   2     UNION ALL SELECT   Third   3       Only for small datasets though  which should be fine for your 4 records

User · Answer

In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement   INSERT INTO MyTable   Column1  Column2   VALUES   Value1  Value2      Value1  Value2     For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008   For example   INSERT INTO MyTable     Column1  Column2  Column3   VALUES     John   123   Lloyds Office         Jane   124   Lloyds Office         Billy   125   London Office        Miranda   126   Bristol Office

User · Answer

NOTE  This answer is for SQL Server 2005  For SQL Server 2008 and later  there are much better methods as seen in the other answers   You can use INSERT with SELECT UNION ALL   INSERT INTO MyTable   FirstCol  SecondCol      SELECT   First   1     UNION ALL SELECT   Second   2     UNION ALL SELECT   Third   3       Only for small datasets though  which should be fine for your 4 records

User · Answer

NOTE  This answer is for SQL Server 2005  For SQL Server 2008 and later  there are much better methods as seen in the other answers   You can use INSERT with SELECT UNION ALL   INSERT INTO MyTable   FirstCol  SecondCol      SELECT   First   1     UNION ALL SELECT   Second   2     UNION ALL SELECT   Third   3       Only for small datasets though  which should be fine for your 4 records

User · Answer

INSERT statements that use VALUES syntax can insert multiple rows  To do this  include multiple lists of column values  each enclosed within parentheses and separated by commas    Example   INSERT INTO tbl name  a b c  VALUES 1 2 3   4 5 6   7 8 9

User · Answer

In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement   INSERT INTO MyTable   Column1  Column2   VALUES   Value1  Value2      Value1  Value2     For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008   For example   INSERT INTO MyTable     Column1  Column2  Column3   VALUES     John   123   Lloyds Office         Jane   124   Lloyds Office         Billy   125   London Office        Miranda   126   Bristol Office

[sql] Inserting multiple rows in a single SQL query?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to insert