Solved

Tempdb IDENTITY as Variable

Posted on 2004-09-15
5
187 Views
Last Modified: 2006-11-17
--Why does this not work?

declare @myNumber integer

set @myNumber = 501

DROP TABLE  [#myTEMP]

CREATE TABLE [#myTEMP]
      (
      [myFIELD] [int] IDENTITY( @myNumber,  1) NOT NULL
      )
0
Comment
Question by:Greg Rowland
5 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 75 total points
ID: 12070413
The CREATE TABLE doesn't allow for a variable, however you can CREATE with value 1 then immediately RESEED to something else :

declare @myNumber integer

set @myNumber = 501

DROP TABLE  [#myTEMP]

CREATE TABLE [#myTEMP]
     (
     [myFIELD] [int] IDENTITY( 1,  1) NOT NULL
     )

DBCC CHECKIDENT ('#myTEMP', RESEED, @MyNumber)
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 75 total points
ID: 12070765
or  you can try global temp table (or regular table):

declare @myNumber integer
declare @str nvarchar(1000)
set @myNumber = 501

DROP TABLE  ##myTEMP

set @str ='CREATE TABLE ##myTEMP
     (
     [myFIELD] [int] IDENTITY(' + cast(@myNumber as varchar(10)) + ',  1) NOT NULL
     )'
--print @str
exec(@str)

select * from ##myTEMP
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question