Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

Question

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'.

Solution 1

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

For SQL Server 2008, can do it in one VALUES clause exactly as per the statement in your question (you just need to add a comma to separate each values statement)...

Solution 2

Your syntax almost works in SQL Server 2008 (but not in SQL Server 2005¹):

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       

---

¹ 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.

Solution 3

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)

Solution 4

Using INSERT INTO ... VALUES syntax like in Daniel Vassallo's answer there is one annoying limitation:

From MSDN

The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000

The easiest way to omit this limitation is to use derived table like:

INSERT INTO dbo.Mytable(ID, Name)
SELECT ID, Name 
FROM (
   VALUES (1, 'a'),
          (2, 'b'),
          --...
          -- more than 1000 rows
)sub (ID, Name);

LiveDemo

---

This will work starting from SQL Server 2008+

Solution 5

You could do this (ugly but it works):

INSERT INTO dbo.MyTable (ID, Name) 
select * from
(
 select 123, 'Timmy'
  union all
 select 124, 'Jonny' 
  union all
 select 125, 'Sally'
 ...
) x

View more solutions

Comments

[abatishchev]

I think that it's better to write more ugly rather than less effective. I mean unnecessary load of database engine

[Code Commander]

Is this any more efficient than using multiple INSERT statements?

[ZygD]

@Code Commander: no, in that it is longer to compile. Yes, in that you have one insert only. But it answers the question: no repeat of the INSERT table (columnlist)

[user2601995]

Works in SQL Server 2012

[Michael]

Server 2008 doesn't allow more than 1000 rows inserted this way.

[VoidKing]

@gbn Thanks for the answer, it seems to be the only way to perform multiple INSERT statements at once in SQL Server CE. I am very curious, though, after reading up on what UNION ALL does, how this actually works, at all. I see the SELECT, the UNION ALL, and of course, the original INSERT INTO statement, but I don't see how this is actually pushing multiple row's values like it is. I am curious because I would like to append UPDATE statements, as well, and wanted to test if this was possible, but before I can do that, I need to understand how this much of it works.

[Rahul Mandaliya]

is it possible to insert data into wordpress table directly means insert data in to mysql and display into wordpress page

[MarioDS]

@VoidKing I know this comes half a year later and you might have figured this out yet long ago, but it's really quite simple. By using select you create a set with columns and rows, and by design these rows can be inserted into another table with an equal amount of columns. You can even use a mixture of literals and values. For example, using insert with select 'A', ID from ATable would insert 'A' in the first column every time and the ID column value of the corresponding row of ATable in the second column.

[JPK]

This also workes with DB2 which is an important sidenote for those of us stuck in outdated technology. The values seperated by comma answer is better in my mind for those of you working in SQL Server 2008 or newer. The OP can remove all "values" except the first and replace with a ,

[Danny Rancher]

Is this possible with a select statement? I'm trying to avoid explicitly creating a #table (temporary table).

[netblognet]

What happens, if one value set is faulty? Will all inserts rolled back or just the faulty row?

[PRMan]

While this works, it took twice as long to load our current database as multiple values statements.

[ZygD]

@PRMan you would not do that after version SQL Server 2008. As mentioned...

[CodeCamper]

Can I have a link to an article about this 'sub' syntax.

[Lukasz Szozda]

@CodeCamper docs.microsoft.com/en-us/sql/t-sql/queries/… section: C. Specifying multiple values as a derived table in a FROM clause

[Mauricio Gracia Gutierrez]

@netblognet I just tested that only faulty rows are not inserted (all of the all the others are inserted correctly)

[Vadim Berman]

The advantage of this answer is that it provides a way to specify identical values without repeating them (which is what I was looking for). E.g. with a third column that is identical, you wouldn't need to repeat it for a thousand times.

[Lukasz Szozda]

@VadimBerman Yes, that is good scenario when there is no default defined on table.

[Lukasz Szozda]

@Michael It could be lifted stackoverflow.com/a/42703601/5070879