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

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

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

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

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

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

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

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