9

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • 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 Dec 21 '15 at 07:37
  • @TimBiegeleisen It doesn't work. I just tried. If you can make it work, show me how. – AngryHacker Dec 21 '15 at 07: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 Dec 21 '15 at 07:46
  • You still never told us what version of SQL Server you are using. Really bad form IMO. – Tim Biegeleisen Dec 21 '15 at 07: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 Dec 21 '15 at 08:07
  • @TimBiegeleisen I've tried it in SQL 2005 and 2014 - it worked in neither. – AngryHacker Dec 21 '15 at 08:28
  • Could you use a loop along with a stored procedure here? Or maybe just add a second non auto increment column. – Tim Biegeleisen Dec 21 '15 at 08:31

1 Answers1

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
Arion
  • 31,011
  • 10
  • 70
  • 88
  • I am trying to do it in a batch operation. E.g. 5 rows with a single insert. Like I did it on MySQL. – AngryHacker Dec 21 '15 at 07:46
  • @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. – Branko Dimitrijevic Dec 21 '15 at 07:56