Post a Comment On: SqlInk

Today we came up with this really interesting scenario. We've got a table that has just one column, and that column happens to be an identity column. Try whatever you will, a regular INSERT statement will not work.

You cannot do any of the following:
INSERT INTO table_name() VALUES()
INSERT INTO table_name(colA) VALUES(default)
INSERT INTO table_name(colA) VALUES(null)
INSERT INTO table_name(colA) VALUES(1)

...so, any guesses?

To solve this issue, you've got to use INSERT with the DEFAULT VALUES clause as follows:
INSERT INTO table_name DEFAULT VALUES

To enable the insertion of explicit values in an identity column, you can set IDENTITY_INSERT to ON.