Solved

Tempdb IDENTITY as Variable

Posted on 2004-09-15
5
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Eugene Z
Eugene Z 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

733 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