If I have a table with only an auto-increment column, how do I insert into it?

Question

A colleague asked me, if you have a table in SQL Server with only an auto-increment column, how do you insert a new row into that table?

INSERT INTO MyTable() VALUES()

...doesn't work.

As for why... I'm not really sure. But I found the question kind of compelling.

Answer 1

Apparently this works:

INSERT INTO MyTable DEFAULT VALUES

(Just discovered it after I hit Ask Question. Sorry!)

Answer 2

set identity_insert MyTable ON

insert MyTable(MyIdentColumn) values(42)

set identity_insert MyTable OFF

Answer 3

Use the IDENTITY_INSERT Setting:

SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable (AutoIncrementColumnName, OtherColumnNames...) VALUES (9999, OtherData...) SET IDENTITY_INSERT MyTable OFF

Make sure you insert a value that isn't in the key already.

Answer 4

insert into mytable default values

Answer 5

use default values, works also with defaults

create table #test (id int identity, Somedate datetime default getdate())


insert #test default values

select * from #test

id Somedate

1 2009-06-30 16:04:03.307