Identity Coulmn - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Identity Coulmn

Author

Topic

samir.first
Starting Member

34 Posts

Posted - 2013-10-07 : 08:00:02

Table Test
ID (Identiy) Code Name
1250 A John
1251 B Coly
1256 C Samir

I need Update Coulmn ID in Table Test

ID (Identiy) Code Name
1 A John
2 B Coly
7 C Samir
And Update All Coulmns Depending on This Column

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-10-07 : 09:27:38

1250 or 1
1256 or 7
it should not matter...

What is the problem you are trying to solve?

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 09:33:48

for that you need to set IDENTITY_INSERT to ON


SET IDENTITY_INSERT Test ON
GO

UPDATE Test
SET ID = ID - 1249
WHERE ID BETWEEN 1250 AND 1256


SET IDENTITY_INSERT Test OFF
GO

I dont know why you want to do this as ID seems to be a primary key

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 09:34:42

also you need to make sure you identify dependencies on these values by other objects (foreign key constraints), remove and remap them to new values after your updates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

samir.first
Starting Member

34 Posts

Posted - 2013-10-07 : 11:21:48

I am transferring data to a new database and i want to reset the make the new tables identity value begins from 1 and i want to update the new ID value to all the related child tables with the correct corresponding value.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 11:31:47

quote:
Originally posted by samir.first

I am transferring data to a new database and i want to reset the make the new tables identity value begins from 1 and i want to update the new ID value to all the related child tables with the correct corresponding value.

You can update the identity columnby setting the identity_insert property on. But, as for updating the dependent columns, there are only two ways I know of that you can accomplish that.

1. Enable cascade updates. There is some info here on how to set that up. http://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx I am not a fan of leaving cascade updates enabled, but this may be one of those cases where you do want to use it, at least temporarily. If you choose to do that, do some testing and understand the implications before actually doing it.

2. Go through all the dependent tables and do manual updates. You would first disable all the constraints, generate a mapping table from the old values to the new values for the key column, then update all the affected tables and reinsert the foreign key constraints.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 00:08:02

quote:
Originally posted by samir.first

I am transferring data to a new database and i want to reset the make the new tables identity value begins from 1 and i want to update the new ID value to all the related child tables with the correct corresponding value.

for that isnt it enough to add a new identity column in new table and transfer data from old table?
The current id column (now without identity property) will preserve its value and new id column will have values starting from 1.
Then in child tables remove current fk constraint to existing id column, do an update based on existing id value to get new generated id value from identity column.
ie


UPDATE c
SET c.FKCol = t.NewIDCol
FROM ChildTable c
JOIN YourTable t
ON t.OldIDcol = c.FKCol

Finally recreate the FK constraint to the new ID column in child tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources