IDENTITY COLUMN for #temp tbl and SEED value with variable.

Question

Post reply

IDENTITY COLUMN for #temp tbl and SEED value with variable.

SSISSSRSSSAS

SSC Enthusiast

Points: 133
More actions
June 22, 2009 at 12:46 pm

#384820

Before I post my code, Let me tell what i am trying to do. For my base table I need to generate ID with int and increase that ID with 1 for every row insert. I will not post all the code here becoz there are too many other logic. But code from test is here.....create table #test (id int, col1 char(10), col2 char(10))
-- drop table #test
GO
insert into #test values
(1, 'eeeee', 'dddddd'),
(2, 'aaaaaa', 'bbbbb'),
(3, 'ffffff', 'fffff'),
(7, 'jjjjjj', 'ddddd')
GO
SELECT * FROM #test
DECLARE @maxvalue int
--SELECT IDENTITY (int ,1, 1) as RowID, * INTO #TMP FROM dbo.TMP_QIZDATA
SELECT @maxvalue = MAX(id) FROM #test
SELECT @maxvalue
select identity (int, 1,1) as Id , col1, col2
into #ABS
from #test
SELECT * FROM #ABS
drop table #ABS
drop table #test
Now, I wanna replace the seed value for IDENTITY column with max value from test1 tbl
SO my code will be something like select identity (int, @maxvalue,1) as Id , col1, col2
into #ABS
from #test
But i cannot do that.
So can someone tell me other ways to do this.? Please don't tell me not to use temp table? I have to becoz of amount of work that I do after inserting data into temp tbl.
All I am trying to do is generate id for temp table where value will be 1 more than the max value from test1 tbl.
thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You can do it without an identity column:

/*

-- replace this code

SELECT @maxvalue

select identity (int, 1,1) as Id , col1, col2

into #ABS

from #test

*/

-- with this

select row_number() OVER (Order by Col1) + @maxvalue AS ID,

Col1,

Col2

into #ABS

from #test

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SSISSSRSSSAS SSC Enthusiast Points: 133 More actions · Answer 2

Thanks Wayne. I thought of other way too, and it worked out. I can just add the @maxvalue to the identity column of temp table before inserting into base table. But, things are changing here a whole lot and so does the requirements.

I will test this and if I need anything for future I will post here.