Update Values in Identity Column

How to update Identity Column in SQL Server?

You can not update identity column.

SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

Although there are some alternatives to achieve a similar kind of requirement.

  • When Identity column value needs to be updated for new records

Use DBCC CHECKIDENT which checks the current identity value for the table and if it's needed, changes the identity value.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)
  • When Identity column value needs to be updated for existing records

Use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT YourTable {ON|OFF}

Example:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

Update values in identity column

You are trying to perform an update, not inserting new rows.

In order to do that, you will need to set identity_insert ON and copy the row you want to update to a new row with the new ID value, then delete the old row (assuming no FK is referencing it)

Something along the lines of:

set identity_insert GeoCountry on
go

insert into GeoCountry (all columns including IDentity column)
select 18, (all columns except IDentity column)
from GeoCountry where CountryID = 250

-- Delete will only work if no referencing FK's
delete GeoCountry where CountryID = 250

set identity_insert GeoCountry off
go

[Given that you are trying to update it, that would suggest it is still in use (i.e. by referencing FK's) and that makes things more complicated...]

How to change identity column values programmatically?

You need to

set identity_insert YourTable ON

Then delete your row and reinsert it with different identity.

Once you have done the insert don't forget to turn identity_insert off

set identity_insert YourTable OFF

Adding an identity to an existing column

You can't alter the existing columns for identity.

You have 2 options,

  1. Create a new table with identity & drop the existing table

  2. Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)
ON [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT *
FROM dbo.Names )
INSERT INTO dbo.Tmp_Names ( Id, Name )
SELECT Id,
Name
FROM dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

Updating Identity Column of a table with consecutive numbers through SQL Stored Procedure

The IDENTITY keword is used to generate a key which can be used in combination with the PRIMARY KEY constraint to get a technical key. Such keys are technical, they are used to link table records. They should have no other meaning (such as a sort order). SQL Server does not guarantee the generated IDs to be consecutive. They do guarantee however that you get them in order. (So you might get 1, 2, 4, ..., but never 1, 4, 2, ...)

Here is the documentation for IDENTITY: https://msdn.microsoft.com/de-de/library/ms186775.aspx.

Personally I don't like it to be guaranteed that the generated IDs are in order. A technical ID is supposed to have no meaning other then offering a reference to a record. You can rely on the order, but if order is information you are interested in, you should store that information in my opinion (in form of a timestamp for example).

If you want to have a number telling you that a record is the fifth or sixteenth or whatever record in order, you can get always get that number on the fly using the ROW_NUMBER function. So there is no need to generate and store such consecutive value (which could also be quite troublesome when it comes to concurrent transactions on the table). Here is how to get that number:

select
row_number() over(order by id),
employeeid,
punch_time,
deviceid
from mytable;

Having said all this; it should never be necessary to change an ID. It is a sign for inappropriate table design, if you feel that need.

How do you UPDATE an identity column in SQL azure? Is it possible?

SET IDENTITY_INSERT only works for INSERTS and that error is not specific to SQL Azure. You'll get the same error on SQL Server/Express

to go around the issue, SET IDENTITY_INSERT to ON, reinsert all the column values from the existing row whose identity value you want to replace with a new value, SET IDENTITY_INSERT OFF, then delete the previous row.

Stored procedures to insert and then update the table based on identity column

First, absolutely yes, scope_identity is the right way to get the Id value.

You say you are storing the returned Id value from your first procedure call (I presume the column is called Id you don't provide the table schema), so you can include a parameter on your second procedure for it (@Id int) and just pass it along with the other values you are already supplying - your comment suggests you are going to do that?

Then in your second procedure you simply

update gds.TBL_SF_INTEGRATION_AUDIT set
<columns=@values>
where id=@id

Another way of capturing the Identity is to use the output clause to capture the identity value along with other correlating values into a separate table.

You can do the following

insert into gds.TBL_SF_INTEGRATION_AUDIT (<columns>) 
output inserted.Id, inserted.Integration_Batch_Id into <another table> -- or any other columns needed
values (<values>)

You can then join to this using the known values to find the Id that was generated.



Related Topics



Leave a reply



Submit