40

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.

James McMahon
  • 48,506
  • 64
  • 207
  • 283
  • 4
    why don't you want to use a column name list? – Mitch Wheat Jun 01 '09 at 15:19
  • @Mitch, it is kind of complex, but I have a program that generates the SQL on the fly and in my original design I wasn't dealing with any identity columns so I excluded column names. I need to do a quick update and I can avoid using column names it would make it that much easier. – James McMahon Jun 01 '09 at 15:28
  • @nemo-- Having been in the "quick patch" situation before, I'll give you a pass on this for now. But please, for your own sake down the road, plan to specify column names in the next full update.... – RolandTumble Jun 01 '09 at 16:57

6 Answers6

70

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.

ma8e
  • 3
  • 1
Eric
  • 92,005
  • 12
  • 114
  • 115
  • 2
    Oh really? That is fantastic. I had just assumed that wouldn't work. Let me give that try. – James McMahon Jun 01 '09 at 15:19
  • @Eric is correct that what's I meant in point 1 above (have edited to show my intent), +1 – Mitch Wheat Jun 01 '09 at 15:21
  • 1
    Eric this isn't working for me at all. It is complaining that I have, identity insert off, I assume because it is trying to insert the first item in values into ID. Anything specific you have to set to get it to ignore the first column? – James McMahon Jun 01 '09 at 15:27
  • Don't put the ID column in the insert statement if you want SQL Server to autogenerate the ID for you. – Eric Jun 01 '09 at 15:29
  • don't set identity insert off when leaving identity column out of the coolumn list, otherwise a value will not be generated for it – Mitch Wheat Jun 01 '09 at 15:30
  • @Eric, I didn't specify any columns. I ran INSERT INTO MyTbl VALUES ('TEST1', 'TEST2', 'TEST3', 'TEST4') on a table with 5 columns, the first being an autogenerated ID column. The database is trying to put 'TEST1' into the ID column. – James McMahon Jun 01 '09 at 15:36
  • I am testing this out on SQL Server 2005 express, just FYI. – James McMahon Jun 01 '09 at 15:41
  • I think my issue is that when I run the query in the SQL server query window it translates it into INSERT INTO (ColumnName, ColumnName ... etc), anyway to avoid this? – James McMahon Jun 01 '09 at 15:50
  • 1
    This does work, I was just hitting some SQL Server GUI strangeness. – James McMahon Jun 01 '09 at 15:56
32

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

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
11

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.

Pang
  • 9,564
  • 146
  • 81
  • 122
A-K
  • 16,804
  • 8
  • 54
  • 74
  • Yeah my boss actually said the same thing. Both of you are completely right, but the data in this database is in-permit (i.e. wiped out and regenerated daily) so if something like this happened it wouldn't be the end of the world. – James McMahon Jun 01 '09 at 17:18
  • I think it would be *better* if the original insert breaks if another column is added - it makes sure the insert caters for all fields (although NOT NULL settings on fields could also be used to enforce entry) – Shiraz Oct 25 '19 at 13:49
7

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, '')
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Just to clarify, by explicit values you mean you have to query the database to see what the next identity in the sequence would be and insert it manually? – James McMahon Jun 01 '09 at 15:16
  • No, you can use any value that is not already there, but the next in sequence will start from the greatest plus one.... – Mitch Wheat Jun 01 '09 at 15:17
  • It tends only to be used when you have something like reference data that is required to have the exact same ID values across different application installations – Mitch Wheat Jun 01 '09 at 15:18
  • 1
    Do NOT ever use Set identity_insert on in a production environment! Not unless you want other users inserting to the table to have issues. This is only to be used when transferring large amounts of data from one system to another and should only be done in single-user mode. – HLGEM Jun 01 '09 at 18:27
  • @HLGEM: I'm not suggesting you leave it turned on! But you are correct that it should ideally be done when there is no activity on that table. We use it mainly on reference data tables, when there is never any insert activity during normal use. – Mitch Wheat Jun 02 '09 at 00:01
2

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
0
set identity_insert customer on
insert into Customer(id,Name,city,Salary) values(8,'bcd','Amritsar',1234)

where 'customer' is table name

Werner Henze
  • 16,404
  • 12
  • 44
  • 69
sohan yadav
  • 59
  • 2