Inserting multiple rows in a single SQL query?

Question

data is like 4 persons in each row their ids and office region. person id office in 3 columns

Answer 1

you can perform multiple operations in a single sql query, just separate the insert statements with semicolons, e.g.

insert into table1 (field1,field2) values (value1,value2);
insert into table1 (field1,field2) values (value1,value2);
insert into table1 (field1,field2) values (value1,value2);
insert into table1 (field1,field2) values (value1,value2)

you can wrap in a transaction if necessary also

Answer 2

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

Answer 3

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.

Answer 4

In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement.

INSERT INTO Table ( Column1, Column 2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )

For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008.

Answer 5

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