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`?

Answer 1

Depending on your DBMS and SQL language SQL server from the 2008 version gives you the ability to add a set of values just by separating comma between the values parenthesis.

INSERT INTO MyTable VALUES

("John", 123, "Lloyds Office"),

("Jane", 124, "Lloyds Office"),

("Billy", 125, "London Office"),

("Miranda", 126, "Bristol Office");

If you're not using SQL Server you can use the SELECT and UNION ALL clauses:

NSERT INTO MyTable

SELECT 'John', 123, 'Lloyds Office'

UNION ALL

SELECT 'Jane', 124, 'LloydsOffice'

...

Good Luck

--Shervin