(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.
This should work:
INSERT INTO GroupTable DEFAULT VALUES
merge operator. E.g. merge into TableName using (values (1), (2), (3)) s(n) on 1=0 when not matched then insert default values;Here you go:
INSERT INTO GroupTable DEFAULT VALUES
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.
GO has to be on its own line. Looks like RMK just formatted his example improperly, by forgetting to put it on a new line. GO is not a T-SQL keyword, rather it's a special command recognized by SSMS on how to process the preceding T-SQLCan 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.
This will work actually--
insert into TABLE default values
