Results 1 to 5 of 5

Thread: Identity

  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Unanswered: Identity

    Suppose I have a table named table1 which has a identity field named "Col1".Now i want to have a backup of this table by running this script
    select * into table1_backup from table1
    I get the backup in the table table1_backup but i miss the identity property for the field col1.
    My question is
    1) How can I get the identity property by running that script?
    2)How can i have the constraints of table1 in table1_backup?
    Subhasish

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Looks like your trying to make an exact copy of the original table including the identity seed?

    To import the identity turn on identity insert like so:

    Set IDENTITY_INSERT table1_backup ON
    Select * into table1_backup from table1
    Set IDENTITY_INSERT table1_backup OFF

    Brent

  3. #3
    Join Date
    Oct 2003
    Posts
    83
    Thanks Bren.
    But what is about my second question?

  4. #4
    Join Date
    Oct 2003
    Posts
    83
    Brent
    This script will not work
    Set IDENTITY_INSERT table1_backup ON
    Select * into table1_backup from table1
    Set IDENTITY_INSERT table1_backup OFF

    Because the table isgetting created in the second stape(Select * into table1_backup from table1)
    So before creating the table how can it's IDENTITY_INSERT property set to on or off?
    Subhasish

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Sorry bout that, this one should work,, takes awhile longer as you need to define the datatypes for each column such as col1 int, col2 varchar(20)

    Create Table table1_backup (col1 col1type, col2 col2type, etc)

    Set IDENTITY_INSERT table1_backup ON
    Insert into table1_backup (col1, col2, etc)
    select col1, col2, etc
    from table1
    Set IDENTITY_INSERT table1_backup OFF

    As for the second question, not sure off the top of my head on importing contraints. I'll look around, but best bet would be to put that question in a new thread here in dbforums.

    Brent


    Originally posted by subhasishray
    Brent
    This script will not work
    Set IDENTITY_INSERT table1_backup ON
    Select * into table1_backup from table1
    Set IDENTITY_INSERT table1_backup OFF

    Because the table isgetting created in the second stape(Select * into table1_backup from table1)
    So before creating the table how can it's IDENTITY_INSERT property set to on or off?
    Subhasish

Posting Permissions

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