CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlidentity
James McMahon
James McMahon

Reputation: 49659

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

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.

Upvotes: 40

Views: 180465

Answers (6)

A-K
A-K

Reputation: 17090

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.

Upvotes: 11

sohan yadav
sohan yadav

Reputation: 69

set identity_insert customer on
insert into Customer(id,Name,city,Salary) values(8,'bcd','Amritsar',1234)

where 'customer' is table name

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300797

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, '')

Upvotes: 8

Eric
Eric

Reputation: 95223

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.

Upvotes: 72

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

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

Upvotes: 33

Tom H
Tom H

Reputation: 47402

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.

Upvotes: 2

Related Questions