CODEWITHSUNDEEP
sqlsql-server-2005
NoviceToDotNet
NoviceToDotNet

Reputation: 10815

sql query multiple record insertion

how can i insert multiple records using single sql statement

Upvotes: 1

Views: 305

Answers (4)

Daniel Vassallo
Daniel Vassallo

Reputation: 344571

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');

Upvotes: 3

Zafer
Zafer

Reputation: 2190

You can insert multiple records from a subquery as well.

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

Upvotes: 0

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3427

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.

Upvotes: 0

Lekensteyn
Lekensteyn

Reputation: 66525

Using MySQL:

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

Upvotes: 0

Related Questions