0

I would like to increase all ids in my table by 1000 cause I need to insert there data from other table with excactly the same ids. What is the best way to do that?

gruber
  • 28,739
  • 35
  • 124
  • 216
  • Already asked here: http://stackoverflow.com/questions/751522/how-to-change-identity-column-values-programmatically – David Feb 16 '11 at 13:53
  • @David - where does it say the id is an identity? – RichardTheKiwi Feb 16 '11 at 14:59
  • It doesn't. it's an assumption. However, given that the id field is an integral value (whether it's an in or a long) I think (and I can be and often am wrong) it's a very reasonable assumption. And even if it's not, the question I linked to already answers this question PLUS deals with the potential of an Identity field. – David Feb 16 '11 at 19:06

3 Answers3

3
update dbo.table set id = id + 1000
go
Geraldo
  • 31
  • 1
0

The best way to go is to not do that. You have to change all related records as well and if you are using identities it gets even more complicated. If you do anything wrong you will seriousl mess up your data integrity. I would suggest that the data you want to insert is the data that needs to have the values changed and if you need to relate back to the data in another tbale, store the original ID in a new field in the table called something like table2id or database2id. If you can't change the existing table, then you can use a lookup table that has both the old id value and the new one.

Under no circumstances should you attempt something of this nature without taking a backup first.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

First as HLGEM it seems to be a bad id (think about your foreign keys on id's you must add 1000 to them to).

Second dbo.table has become sys.tables in Server 2008.

Finally you'll need to find the foreign keys columns with this request :

    SELECT name,OBJECT_NAME(object_id)
    FROM sys.columns
    WHERE name like '%id' or name like 'id%' 
--depends on where is 'id' in your columns names

name : the column name, OBJECT_NAME : the table name

And update the whole thing (with a tricky request that should looks like this one, but i didn't test with the "update" command) :

CREATE TABLE #TablesWithIds (
    columnName varchar(100),
    tableName  varchar(100)
)

Insert into #TablesWithIds
SELECT name as columnName,OBJECT_NAME(object_id) as tableName
FROM sys.columns
WHERE name like '%id%' 

update #TablesWithIds.tableName  set #TablesWithIds.columnName = #TablesWithIds.columnName +1000

drop table #TablesWithIds
Etienne
  • 46
  • 4