Issue with Identity column - 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 2008 Forums

Transact-SQL (2008)

Issue with Identity column

Author

Topic

Ravi0435
Starting Member

47 Posts

Posted - 2011-08-22 : 18:40:47

Hi..
Why doesnt this work? why does it not take the seed value from a variable? i Dont understand thew error.

Declare @seed_2 bigint
Set @seed_2 = 1070001000000008136

Alter Table GROUPEDFORPK
Add AssignedHeaderPK bigint identity(@seed_2,1)

Error: Says
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@seed_2'

Thanks,
Ravi

Nothing much that i can do..!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-22 : 18:42:16

You will need to use dynamic SQL for that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ravi0435
Starting Member

47 Posts

Posted - 2011-08-22 : 18:57:21

Thanks for the reply/suggestion.

I tried that as well and the following doesnt work either and says - incorrect syntax near @Database.

set @sql = '

USE ' + @Database + '
Alter Table RAW_CLAIM_GROUPEDFORPK
Add AssignedHeaderPK bigint identity('+cast((Select (MAX(AssignedHeaderPK)+1) From '+ @Database'+.dbo.RAW) as bigint)+',1)'

--print @sql
exec (@sql)

Nothing much that i can do..!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-22 : 19:03:07

Let's try your first example as you've really complicated the code with your new post. Does the first example work with dynamic SQL?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ravi0435
Starting Member

47 Posts

Posted - 2011-08-22 : 19:11:49

No it did not work...asssume that i need to re-write the first one in to Dynamic SQL how would you write it?

The first code when i write it in Dynamic SQL = second code.
the reason it looks complicated is because instead of temp variable i substituted the whole SQL statement, let me put it this way below might be easier.

set @sql = '

USE ' + @Database + '
Alter Table RAW_CLAIM_GROUPEDFORPK
Add AssignedHeaderPK bigint identity('+cast(@seed_2) as bigint)+',1)'

and we have
@Database = HCN
@seed_2 = Select (MAX(AssignedHeaderPK)+1) From HCN.dbo.RAW

Nothing much that i can do..!!

Ravi0435
Starting Member

47 Posts

Posted - 2011-08-22 : 20:32:49

Thanks tkizer
Answer:
http://www.dbforums.com/microsoft-sql-server/983541-using-variable-assign-identity-seed.html

CREATE TABLE TestTbl(i int IDENTITY(1, 1), j int)
DECLARE @x int
SET @x = 2179
DBCC CHECKIDENT (TestTbl, RESEED, @x)
INSERT INTO TestTbl (j) VALUES(5)
SELECT * FROM TestTbl

Nothing much that i can do..!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-22 : 20:33:56

You can't nest your query in there like that.

Let's start with the simple example:

Declare @seed_2 bigint
Set @seed_2 = 1070001000000008136

Alter Table GROUPEDFORPK
Add AssignedHeaderPK bigint identity(@seed_2,1)

And now let's rewrite it:

Declare @seed_2 bigint, @sql nvarchar(4000)
Set @seed_2 = 1070001000000008136

set @sql = 'Alter Table GROUPEDFORPK Add AssignedHeaderPK bigint identity(' + convert(varchar(20), @seed_2) + ',1)'

exec @sql

Does that work?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2011-08-23 : 02:32:42

Can you use

DBCC CHECKIDENT ('GROUPEDFORPK', RESEED, 1070001000000008136)

after adding the column, or do you need it to assign IDs to existing records starting from that number?

(Probably the later, in which case this won't help

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources