How to insert into a table with just one IDENTITY column?

Question

(Came up with this question in the course of trying to answer this other one)

Consider the following MS-SQL table, called GroupTable:

GroupID
-------
1  
2  
3

where GroupID is the primary key and is an Identity column.

How do you insert a new row into the table (and hence generate a new ID) without using IDENTITY_INSERT ON?

Note that this:

INSERT INTO GroupTable() Values ()

... won't work.

edit: we're talking SQL 2005 or SQL 2008 here.

DJ. · Accepted Answer · 2009-05-11T22:23:36.273

129

This should work:

INSERT INTO GroupTable DEFAULT VALUES

edited May 11 '09 at 22:23

answered May 11 '09 at 22:13

DJ.

I can't get this to work with Visual Studio 2008/SQL Express 2005. Any ideas? Same table layout, one column, primary key, identity(1,1). – Thomas Sandberg Aug 31 '09 at 18:27
I'm using SQL 2008 R2, no joy for me either! – TDaver Nov 05 '11 at 14:57
Works for me on SQL Server 2008 Express. – Adrian Lynch Feb 19 '12 at 12:59
3

@RomanPekar, in SqlServer 2008 and later it is possible with `merge` operator. E.g. `merge into TableName using (values (1), (2), (3)) s(n) on 1=0 when not matched then insert default values;` – i-one Nov 06 '15 at 17:29

score 17 · Answer 2 · answered May 11 '09 at 22:14

17

Here you go:

INSERT INTO GroupTable DEFAULT VALUES

answered May 11 '09 at 22:14

tofi9

score 3 · Answer 3 · answered Oct 28 '16 at 11:35

3

It is possible to insert more than one row at a time.

For e.g., to insert 30 rows. INSERT INTO GroupTable DEFAULT VALUES GO 30

This will insert 30 rows by incrementing the identity column each time.

answered Oct 28 '16 at 11:35

RMK

Not sure about SQL 2008, but in 2008R2 this does not work (I get the generic error "Invalid syntax near 'GO'") – Elaskanator Jun 13 '18 at 13:47

score 0 · Answer 4 · answered May 11 '09 at 22:12

0

Can you try using a Sequence or something similar? Where you select from a Sequence and it will give you the next value in the sequence.

answered May 11 '09 at 22:12

Mike Pone

I think he's talking about Oracle DB – codeulike May 11 '09 at 22:26
I know sequences exist in Oracle and wasn't sure what (if any) comparable thing existed in SQL Server. That is why I suffixed it with "or something similar" and then gave a definistion of a Sequence for reference. – Mike Pone May 12 '09 at 15:48
3

SQL Server 2012 has [SEQUENCE](http://msdn.microsoft.com/en-us/library/ff878091.aspx)s. – Nick Chammas May 24 '12 at 21:56

4 Answers4