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,
Create a new table with identity & drop the existing table
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
How to Check If an SQL Result Contains a Newline Character
Need SQL Query to Find Parent Records Without Child Records
Generating Random Number in Each Row in Oracle Query
How to Select the Most Frequently Appearing Values
How to Correctly Do Upsert in Postgres 9.5
SQL Server If Not Exists Usage
SQL 2005 Split Comma Separated Column on Delimiter
Is Order in a Subquery Guaranteed to Be Preserved
Postgresql: Fill Null Values in Timeserie Query with Previous Value
Equivalent Function to Stuff in SQL (Group_Concat in Myssql/Listagg in Oracle)
Calling a Stored Procedure in Oracle with in and Out Parameters
How to Connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)
Oracle SQL Where Clause to Find Date Records Older Than 30 Days
Get the Nearest Longitude and Latitude from Mssql Database Table
How to Create Sequence If Not Exists
How to Solve "Either the Parameter @Objname Is Ambiguous or the Claimed @Objtype (Column) Is Wrong."