Handling identity columns in an "Insert Into TABLE Values()" statement?

sql sql-server database tsql identity
James McMahon picture James McMahon · Jun 1, 2009 · Viewed 141.5k times · Source

In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following?

Insert Into TableName Values(?, ?, ?)

My goal is to NOT use the column names at all.

Answer

Eric picture Eric · Jun 1, 2009

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    ADDRESS

Then 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.

Related questions

Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Read More
Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( …

Read More
SQL Server: Get data for only the past year

I am writing a query in which I have to get the data for only the last year. What is the best way to do this? SELECT ... FROM ... WHERE date > '8/27/2007 12:00:00 AM'

Read More