Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Manchester UK
    Posts
    73

    Unanswered: reset IDENTITY after table data import?

    I have a remote DB I am wokring with at present. The DBA has provided me with a non owner LOGIN so I can't copy tables from the live to the staged DB as objects I can only copy tables and data.

    The PKEY and IDENTITY COLUMNS get reset to just regular columns on each table. I can restore the PKEY constraint and have come across the DBCC CHECKIDENT to get the new ident value. I just can't figure out how to set a column to be an identity. The ALTER TABLE command isn't having any of it.

    I am obviously missing the right bit on Books online

    any suggestions?

    many thanks

    Steve
    I haven't lost my mind, there's a backup on one of these floppies, somewhere.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There isn't any way I know to add the IDENTITY property to an existing column. I'm pretty sure that it must be done at the time of the table creation.

    My advice would be to script the table from the production DB, change the script to use IDENTITY, then copy the data from production to your working table.

    -PatP

Posting Permissions

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