How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement?
I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports".
Here's what I want to do, but the syntax is not exactly right... please, someone who has done this before, help me out :)
INSERT INTO dbo.MyTable (ID, Name)
VALUES (123, 'Timmy'),
(124, 'Jonny'),
(125, 'Sally')
I know that this is close to the right syntax. I might need the word "BULK" in there, or something, I can't remember. Any idea?
I need this for a SQL Server 2005 database. I've tried this code, to no avail:
DECLARE @blah TABLE
(
ID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
INSERT INTO @blah (ID, Name)
VALUES (123, 'Timmy')
VALUES (124, 'Jonny')
VALUES (125, 'Sally')
SELECT * FROM @blah
I'm getting Incorrect syntax near the keyword 'VALUES'.
INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'
对于SQL Server 2008，可以完全按照您的问题中的语句在一个VALUES子句中执行它（您只需添加一个逗号来分隔每个值语句）...
Your syntax almost works in SQL Server 2008 (but not in SQL Server 20051):
CREATE TABLE MyTable (id int, name char(10));
INSERT INTO MyTable (id, name) VALUES (1, 'Bob'), (2, 'Peter'), (3, 'Joe');
SELECT * FROM MyTable;
id | name
---+---------
1 | Bob
2 | Peter
3 | Joe
1 When the question was answered, it was not made evident that the question was referring to SQL Server 2005. I am leaving this answer here, since I believe it is still relevant.
If your data is already in your database you can do:
INSERT INTO MyTable(ID, Name)
SELECT ID, NAME FROM OtherTable
If you need to hard code the data then SQL 2008 and later versions let you do the following...
INSERT INTO MyTable (Name, ID)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
上一篇: 获取插入行身份的最佳方法？