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)