May 2, 2007 at 2:21 pm
Is it not possible to set the seed and increment params of the IDENTITY property with variables? I need to import data, incrementing the primary key in the destination table accordingly (which is not an IDENTITY field). I was hoping to create a table variable with new Ids and join to it in the INSERT stmt, but the following gives me a syntax error.
DECLARE @maxNum int
DECLARE @increment smallint
SET @maxNum = 1000 --hardcoded for simplicity, actually is a SELECT stmt
SET @increment = 1
DECLARE @OldNewIds table (idOld int, idNew int IDENTITY(@maxNum + @increment, @increment))
May 2, 2007 at 2:33 pm
CREATE TABLE #tmp(id int IDENTITY, other VARCHAR(100))
INSERT INTO #tmp(other) VALUES ('1st value')
DBCC checkident('#tmp', RESEED, 5)
INSERT INTO #tmp(other) VALUES ('2nd value')
DECLARE @aSeed INT
SET @aSeed = 25
DBCC checkident('#tmp', RESEED, @aSeed)
INSERT INTO #tmp(other) VALUES ('2nd value')
SELECT * FROM #tmp
DBCC checkident allows a variable for reseed.
daralick
May 2, 2007 at 2:55 pm
Ugh, what a hassle. I have to do this for about 50 tables, anybody know an easier way?
May 2, 2007 at 3:09 pm
Ok, well this works too but isn't much prettier. Still seems like there should be some slick in-line way to do it...
DECLARE @maxNum int
DECLARE @increment smallint
SET @maxNum = (SELECT max_num FROM hpl.Table_List WHERE Table_Name = 'Wards')
SET @increment = (SELECT increment FROM hpl.Table_List WHERE Table_Name = 'Wards')
--create temp table with old and new Ids
DECLARE @OldNewIds table (idOld int, idNew int)
INSERT INTO @OldNewIds (idOld) SELECT WardId FROM hpl.Wards
UPDATE @OldNewIds
SET @maxNum = idNew = @maxNum + @increment
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply