Post reply

Passing variable to alter table add id identity(@xy,1)

  • April 3, 2009 at 12:19 am

    #132451

    Is there any why to pass variable to command:

    alter table add id identity(@xy,1)?

    Tnx

  • April 3, 2009 at 12:21 am

    #971318

    After lot of searching I found the answer:

    "

    Identity function doesn't support variables for the seed or increment. So you have several options since you are creating a temporary table:

    1. Create the temporary table without the identity column and then do an ALTER TABLE to add the identity column using dynamic SQL like:

    exec('alter table #prod_note add m_key int identity(' + @max_note_val + ', 1)')

    2. Create an empty table using CREATE TABLE and then use DBCC CHECKIDENT (this accepts variable for seed parameter) to reseed the table before doing the insert. This will be fine if you are dumping small number of rows into the temporary table & performance is not the main criteria. The main reason being that SELECT INTO is the fastest way to create a temporary table with data and doing CREATE followed by INSERT...SELECT will be slower.

    "

  • April 3, 2009 at 1:20 am

    #971328

    matija (4/3/2009)

    Is there any why to pass variable to command:

    alter table add id identity(@xy,1)?

    Tnx

    Try this

    create table new_one(name varchar(30), city varchar(20))

    declare @xy int

    set @xy = 100

    exec('alter table new_one add id1 int identity('+@xy+',1)')

Viewing 3 posts - 1 through 2 (of 2 total)

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