CODEWITHSUNDEEP
sqlsql-serveridentity
codeulike
codeulike

Reputation: 23064

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

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

Upvotes: 91

Views: 27395

Answers (5)

manoj rajupeta
manoj rajupeta

Reputation: 1

This will work actually--

insert into TABLE default values

Upvotes: -1

RMK
RMK

Reputation: 29

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.

Upvotes: 2

DJ.
DJ.

Reputation: 16247

This should work:

INSERT INTO GroupTable DEFAULT VALUES

Upvotes: 140

tofi9
tofi9

Reputation: 5853

Here you go:

INSERT INTO GroupTable DEFAULT VALUES

Upvotes: 17

Mike Pone
Mike Pone

Reputation: 19320

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.

Upvotes: 0

Related Questions