Friday, August 18, 2006
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.
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.
# posted by Nitin Reddy Katkam @ 8/18/2006 03:36:00 PM 
