identity seed - 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 2000 Forums

Transact-SQL (2000)

identity seed

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-02-15 : 15:03:05

Has anyone ever used a variable as an identity seed?

If so, Can you tell me how to..?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 15:24:32

what do you mean a variable as identity seed? can you give an example?

Go with the flow & have fun! Else fight the flow

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-02-15 : 16:23:02

Select identity(int,@MyVariable,1) as fldKey into #tblTemp

I know I can do this with dynamic sql but, would rather be a bit my structured if possible.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-16 : 07:49:38

unfortunatly you can't do that.

Go with the flow & have fun! Else fight the flow

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-02-16 : 09:36:34

This does the job .... I am trying to make it a bit cleaner ....
Any idea?

declare @nextcomval int,@ExecVar varchar(8000)

set @nextcomval = (select fldnextnum from A where fldtablename = 'S')
set @ExecVar = 'set nocount on '
set @ExecVar = @ExecVar+ 'select IDENTITY(int,'+convert(varchar(12),@nextcomval)+',1) fldNum,fldDescr fldNameCol '
set @ExecVar = @ExecVar + 'into #mbTemp from A.S select * from #mbtemp'
exec(@ExecVar)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-16 : 09:44:33

you do realize that in this way you won't be able do access the temp table you create with EXEC with normal sql, don't you?

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-02-16 : 14:37:57

Can you create the table as normal and then use DBCC CHECKIDENT to set the identity to what you want?

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 14:46:05

how is this different from

Declare @myTable table (id identity(1,1), id_offset int, blah blah...)
Insert Into @myTable
Select id_offSet = null, blah blah....

Declare @offsetBy int
Set @offsetBy = 124

Update @myTable
Set id_offSet = id + @offsetBy

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources