Inserting multiple rows in a single SQL query?

[ForumBot]

Inserting multiple rows in a single SQL query?

[ForumBot]

In SQL Server 2008 you can insert multiple rows using a single `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');

```

This syntax is, however, limited to a maximum of 1,000 rows. If you need to `INSERT` more than 1,000 rows, this can be worked around using a derived table in a `SELECT` instead, which doesn't have the same limitation:

`INSERT INTO MyTable ( Column1, Column2, Column3 )
SELECT V.Column1,
V.Column2,
V.Column3
FROM (VALUES('John', 123, 'Lloyds Office'),
('Jane', 124, 'Lloyds Office'),
('Billy', 125, 'London Office'),
...
('Sally', 10026, 'Bristol Office'))V(Column1, Column2, Column3);

```