Issues using IDENTITY - 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)

Issues using IDENTITY

Author

Topic

kadams21
Starting Member

3 Posts

Posted - 2006-12-04 : 15:04:29

I am having problems assigning a seed to an IDENTITY column. Here's a little background - I am attempting to create a table that will have a column containing a unique identifier. This table will be created monthly and in turn use up a set of unique identifiers. This month I am hoping to start at the last identity number and increment from there. Last month a thousand records were created so 1-1000 we assigned. Now I need to start from 1001 and move forward but don't want to hard code the seed. Here's what I tried:

DECLARE @MAXREF BIGINT
SET @MAXREF = (SELECT MAXREF FROM [dbo].[P_HOLD]) --THIS HOLD THE LAST NUMBER NUMBER
SET @MAXREF = @MAXREF + 1

DROP TABLE [dbo].[STDPMT]
SELECT IDENTITY(BIGINT, @MAXREF, 1) AS REFNBR, A.*
INTO [dbo].[TEST]
FROM DBO.T_TEST A

This returns a syntax error and it appears as though IDENTITY doesn't allow for the use of variables. Can I make it work using this method? Or do I need to scrap this idea and try a different direction.

Thanks.
-KA

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-04 : 16:36:02

You'll have to use dynamic SQL like this

DECLARE @MAXREF BIGINT
DECLARE @sql nvarchar(4000)
SET @MAXREF = (SELECT MAXREF FROM [dbo].[P_HOLD]) --THIS HOLD THE LAST NUMBER NUMBER
SET @MAXREF = @MAXREF + 1
DROP TABLE [dbo].[STDPMT]
SET @sql = 'SELECT IDENTITY(BIGINT, ' + cast(@MAXREF as nvarchar(20)) + ', 1) AS REFNBR, A.*
INTO [dbo].[TEST]
FROM DBO.T_TEST A'
EXEC (@sql)

kadams21
Starting Member

3 Posts

Posted - 2006-12-04 : 16:55:53

Awesome. I had started down that road but did so without converting the variable to varchar. I really appreciate the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources