Inserting multiple rows in a single SQL query?

This question already has an answer here:

How do I insert multiple rows WITHOUT repeating the “INSERT INTO dbo.Blah” part of the statement? 12 answers

---

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

---

如果您插入到单个表中，则可以像这样编写查询（可能仅在MySQL中）：

INSERT INTO table1 (First, Last)
VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith');

---

NOTE: This answer is for SQL Server 2005. For SQL Server 2008 and later, there are much better methods as seen in the other answers.

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.

链接地址: http://www.djcxy.com/p/2762.html

上一篇: 我如何使用SQL中的JOIN做UPDATE语句？

下一篇: 在单个SQL查询中插入多行？