sql query multiple record insertion

Question

how can i insert multiple records using single sql statement

Answer 1

Using MySQL:

INSERT INTO TABLE (col1, col2) VALUES
  (val1a, val1b),
  (val2a, val2b),
  (valNa, valNb);

Answer 2

You should use table types and use bulk insert.

EDIT: http://msdn.microsoft.com/en-us/library/bb510489.aspx

This can explain further on using either one. This works for SQL server, I am not so sure about other databases. Both are very useful for a lot of records to be inserted at one go.

Answer 3

For SQL Server 2005 you could do the following:

INSERT INTO your_table (id, field_1, field_2)
   SELECT 1, 'some-data-a', 'some-data-1'
   UNION ALL
   SELECT 2, 'some-data-b', 'some-data-2'
   UNION ALL
   SELECT 3, 'some-data-c', 'some-data-3'
   UNION ALL
   SELECT 4, 'some-data-d', 'some-data-4';

In most modern DBMSes, including SQL Server 2008 and MySQL 5, you could use a much neater syntax:

INSERT INTO your_table (id, field_1, field_2) VALUES
   (1, 'some-data-a', 'some-data-1'),
   (2, 'some-data-b', 'some-data-2'),
   (3, 'some-data-c', 'some-data-3'),
   (4, 'some-data-d', 'some-data-4');

Answer 4

http://msdn.microsoft.com/en-us/library/ms188365.aspx

Answer 5

You can insert multiple records from a subquery as well.

INSERT INTO TABLE (col1, col2) (select a,b from SUB)