Handling identity columns in an Insert Into TABLE Values statement

Question

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

User · Answer

You have 2 choices   1  Either specify the column name list  without the identity column    2  SET IDENTITY INSERT tablename ON  followed by insert statements that provide explicit values for the identity column  followed by SET IDENTITY INSERT tablename OFF   If you are avoiding a column name list  perhaps this  trick  might help    -- Get a comma separated list of a table s column names SELECT STUFF   SELECT        COLUMN NAME AS  text    FROM  INFORMATION SCHEMA COLUMNS WHERE  TABLE NAME    TableName  Order By Ordinal position FOR XML PATH        1 1

User · Answer

Another  trick  for generating the column list is simply to drag the  Columns  node from Object Explorer onto a query window

User · Answer

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

User · Answer

set identity insert customer on insert into Customer id Name city Salary  values 8  bcd   Amritsar  1234    where  customer  is table name

User · Answer

Since it isn t practical to put code in a comment  in response to your comment in Eric s answer that it s not working for you     I just ran the following on a SQL 2005 box  sorry  no 2000 handy  with default settings and it worked without error   CREATE TABLE dbo Test Identity Insert       id  INT IDENTITY NOT NULL      my string   VARCHAR 20  NOT NULL      CONSTRAINT PK Test Identity Insert PRIMARY KEY CLUSTERED  id    GO  INSERT INTO dbo Test Identity Insert VALUES   test   GO  SELECT   FROM dbo Test Identity Insert GO   Are you perhaps sending the ID value over in your values list  I don t think that you can make it ignore the column if you actually pass a value for it  For example  if your table has 6 columns and you want to ignore the IDENTITY column you can only pass 5 values

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql

Examples related to identity