Inserting Into Sql Server Identity and Default Columns
For some reason I always try to insert default or null into Sql Server identity or default columns. So that I remember here is how to do it when not explicitly listing the columns list. When inserting into a table with an identity column, exclude the identity column from the values column list. e.g. Pretend the columns doesn't exist. When inserting into a table with a default column, use the DEFAULT keyword. DECLARE @Test TABLE( id INT IDENTITY(1,1), fname VARCHAR(30) not null, lname VARCHAR(30) not null, ts DATETIME DEFAULT GETDATE() ) INSERT INTO @Test VALUES('Joe', 'Somebody', DEFAULT) SELECT * FROM @Test GO Which results in: id fname lname ts --- ------ -------- ----------------------- 1 Joe Somebody 2011-01-30 12:37:20.353 (1 row(s) affected)