How to insert data in a batch? [duplicate]

Question

In MySQL, I could fire off these statements to insert 5 rows in one shot:

CREATE TABLE t (id int primary key auto_increment)
INSERT INTO t VALUES (DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT),(DEFAULT)

How can I do the same thing in MS SQL Server?

P.S. The reason that the suggested duplicate doesn't solve the problem is that it has tables with non-identity columns. My Table only has an identity column.

If you are using SQL Server 2008 or later, your question will work. For 2005 and earlier, you have to get more creative. In any case, this question is a dupe. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 21, 2015 at 7:37
@TimBiegeleisen It doesn't work. I just tried. If you can make it work, show me how. — AngryHacker
– AngryHacker, Commented Dec 21, 2015 at 7:45
@TimBiegeleisen Specifically the error is An explicit value for the identity column in table 't' can only be specified when a column list is used and IDENTITY_INSERT is ON. — AngryHacker
– AngryHacker, Commented Dec 21, 2015 at 7:46
You still never told us what version of SQL Server you are using. Really bad form IMO. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 21, 2015 at 7:47
You could try turning off the auto increment, but then you would lose the functionality of SQL Server keeping track of the id. Maybe you should consider a change to your schema. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 21, 2015 at 8:07

Arion · Accepted Answer · 2015-12-21 07:43:03Z

3

If you have this definition:

CREATE TABLE t (id int IDENTITY(1,1) PRIMARY KEY)

Then you can do this:

INSERT t DEFAULT VALUES
INSERT t DEFAULT VALUES
INSERT t DEFAULT VALUES
INSERT t DEFAULT VALUES
INSERT t DEFAULT VALUES

answered Dec 21, 2015 at 7:43

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AngryHacker Over a year ago

I am trying to do it in a batch operation. E.g. 5 rows with a single insert. Like I did it on MySQL.

Branko Dimitrijevic Over a year ago

@AngryHacker I don't think SQL Server allows that when trying to insert only into auto-incremented column. If you had other columns, you could use MySQL-style syntax.

Collectives™ on Stack Overflow

How to insert data in a batch? [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related