SQL-How to Insert Row Without Auto incrementing a ID Column?

sql sql-server auto-increment
SpoiledTechie.com picture SpoiledTechie.com · Jun 26, 2009 · Viewed 56.5k times · Source

I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this be the auto incrementing column.

So, here is the problem. I have to insert into this table an ID for the column that has already been created and removed from the table. Kind of like resurrecting this ID and putting it back into the table.

So how can I do this programatically do this without turning the column increment off. Correct me if I am wrong, if I turn it off programatically, It will restart at 0 or 1 and I don't want that to happen...

Answer

Charles Bretana picture Charles Bretana · Jun 26, 2009

If you are in Microsoft SQL Server, you can "turn off" the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in:

  Set Identity_Insert [TableName] On
  -- --------------------------------------------
  Insert TableName (pkCol, [OtherColumns])
  Values(pkValue, [OtherValues])
  -- ---- Don't forget to turn it back off ------
  Set Identity_Insert [TableName] Off

Related questions

How do I add a auto_increment primary key in SQL Server database?

I have a table set up that currently has no primary key. All I need to do is add a primary key, no null, auto_increment. I'm working with a Microsoft SQL Server database. I understand that it can't be …

Read More
MSSQL Select statement with incremental integer column... not from a table

I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on. This column does not actually resides in …

Read More
SQL Server 2005 ROW_NUMBER() without ORDER BY

I am trying to insert from one table into another using DECLARE @IDOffset int; SELECT @IDOffset = MAX(ISNULL(ID,0)) FROM TargetTable INSERT INTO TargetTable(ID, FIELD) SELECT [Increment] + @IDOffset ,FeildValue FROM SourceTable WHERE [somecondition] TargetTable.ID is not an identity …

Read More