Handling identity columns in an "Insert Into TABLE Values()" statement?
By default, if you have an identity column, you do not need to specify it in the VALUES section. If your table is:
ID NAME ADDRESSThen you can do:
INSERT INTO MyTbl VALUES ('Joe', '123 State Street, Boston, MA')This will auto-generate the ID for you, and you don't have to think about it at all. If you SET IDENTITY_INSERT MyTbl ON, you can assign a value to the ID column.
Another "trick" for generating the column list is simply to drag the "Columns" node from Object Explorer onto a query window.
The best practice is to explicitly list the columns:
Insert Into TableName(col1, col2,col2) Values(?, ?, ?)Otherwise, your original insert will break if you add another column to your table.