I’ve been a SQL Practitioner since 1996 when 6.5 was the hot item.  Along the way I’ve learned a bunch of stuff about SQL Server.  Without a doubt, the more I learn, the more I know what I don’t know.  Being humbled on a regular basis is probably a good thing, but sometimes it’s just down right ridiculous what I don’t know.

Most of my database experience is in the data warehouse/mart space, where identity columns are used almost exclusively for the surrogate key.  There have been too many times to count over the years where I’ve had to load data into a table that had an identity column.

For the longest time I was manually typing all of the column names when loading data into a table.  Then I learned through Raman Renak’s article that you can capture the names for all the columns in a table through SSMS just by dragging it from the object explorer to the query window.  I thought that was a breakthrough!  All I had to do was not include the IDENTITY column from the dragged list of columns.

Now I’ve learned that when doing an INSERT to a table the IDENTITY column does not need to be explicitly listed as long as no other columns are specified in the optional column list.  This makes sense since you can’t specify the value for an IDENTITY column anyway.

Thanks to the folks at SQLServerCentral.com for this time saving lesson…  Rather than reproducing the lesson here just go the SQL Server Central for the original lesson plan!

http://www.sqlservercentral.com/Questions/Tags/T-SQL/T-SQL/

Happy SQL-ing!