Create identity column using dynamic seed value

Question

Create identity column using dynamic seed value

M Chabot

SSCrazy

Points: 2511
More actions
July 28, 2005 at 1:06 pm

#168090

I am trying to create a temporary table with a seed value pulled from a stored procedure and I can't get it to work. I cannot seem to use a variable for the seed value in the create table statement. The alternative using dynamic SQL does not work either, I believe because the temporary table disappears instead of being passed back to the code that called the sp_ExecuteSQL statement. The same restrictions seem to be in place for table variables. What can I do? Thank you.

Sample code that does not actually end up creating a table:
DECLARE @SQLStr nvarchar(4000)
DECLARE @RecNum int
SET @RecNum = 1

SET @SQLStr = N'DECLARE @tblTemp TABLE (fldRecNum INT IDENTITY('+ CAST(@RecNum AS varchar(10))+',1) PRIMARY KEY'

EXECUTE sp_ExecuteSQL @SQLStr

Viewing 7 posts - 1 through 6 (of 6 total)

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

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

When you use EXEC or sp_executesql, the code actually executes in a separate SQL batch than the one issuing the EXEC or sp_executesql command. This means a single hash (#) won't be seen by your original batch (the stored procedure) because it's a local temporary table. If you want to go down this road, you'll need to use two hashes (##) in the name, making the temporary table a global one. Obviously, unless you take pains to generate different temporary table names, you can't have this stored procedure running more than one at a time.

K. Brian Kelley
@kbriankelley

M Chabot SSCrazy Points: 2511 More actions · Answer 2

Thank you. Can this same trick be used for table variables? From what I understand, table variables are better than temporary tables. If I can use this trick for table variables, would a DROP TABLE command work to free up the global table variable?

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 3

If you're using a table variable, the table variable is going to disappear when the stored procedure that defined it ends.

From BOL:

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

K. Brian Kelley
@kbriankelley

vadba SSChampion Points: 11133 More actions · Answer 4

Try this example:

CREATE TABLE #temp

(

id int IDENTITY(1,1)

, mydata varchar(20)

)

DECLARE @seed int

SET @seed = 12345

DBCC CHECKIDENT ('#temp', RESEED , @seed) WITH NO_INFOMSGS

INSERT #temp (mydata) VALUES ('ABC')

SELECT * FROM #temp

DROP TABLE #temp

M Chabot SSCrazy Points: 2511 More actions · Answer 5

Thanks for the example. The DBCC CHECKIDENT command works when I run it as the sys admin, but not as the dbo of a database. Brian mentioned in another thread that for this DBCC call to work, the user running the script needs to have elevated permissions for the TempDB, which may be a security issue.

feli1998 Valued Member Points: 53 More actions · Answer 6

I tried the script as well. It works fine.

The only difference is my seed value comes from reading a table instead of a static value:

DECLARE @Seed int

SELECT @Seed=TableIdentity FROM TableData WHERE TableDataID = 1;

DBCC CHECKIDENT('Project', RESEED, @Seed) WITH NO_INFOMSGS

Thank you very much for tip. It really helps Me.

Felix