Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Angry Unanswered: using a variable to assign an IDENTITY SEED

    I'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get errors so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: using a variable to assign an IDENTITY SEED

    Error details please!

  3. #3
    Join Date
    Feb 2004
    Posts
    6

    Re: using a variable to assign an IDENTITY SEED

    Sorry, what i should have said is....
    I'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get incorrect syntax near (@variable) errors when trying to save the stored procedure. so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you happen to be inserting this data into the table with no identity column, then you can do something like this:

    create table target
    (col1 int,
    col2 varhcar(10),...)

    create table #temptarget
    (col1 int identity (1, 1)
    col2 varchar(10), ....)

    insert into target
    select t.col1 + i.baseid, t.col2, ....
    from #temptarget t , (select max(col1) from target) i

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: using a variable to assign an IDENTITY SEED

    Originally posted by joejcheng
    Error details please!
    Yeah and how about the code?

    And why are you doing this? How are you going to manage the values?

    Look at SET IDENTITY_INSERT <tablename> OFF


    But it sounds like their is a design issue.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    6

    Re: using a variable to assign an IDENTITY SEED

    Thanks for all your help, this was the solution provided by VyasKN

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •