Post reply

Help me out in Migration !

  • March 23, 2004 at 12:32 am

    #160660

    Dear all,

    I have two databases in different sql server installation, with exactly the same schema.

    The database have tables which have identity primary key generated by autonumber. These primary keys are used by others table as foreign key.

    I need to copy all data from one location to the other.

    The problem is regarding primary key preservation, because it is generated by autonumber and used as foreign key. For example, the destination table has primary key value of 1 until 1000, and so does the table I want to move. I wonder how could I change the source table's primary key to be 1001 to 2000 (continuing the destination primary key)while preserving the foreign key at all tables related ?

    Thank you in advance.

    regards,

    Annaraj

  • March 23, 2004 at 3:22 am

    #499983

    A possibility... on the destination server:

    set identity_insert MyMainTable on
insert MyMainTable (IdCol, col2, col3...)

  select IdCol+1000, col2, col3...
  from SourceServer.MyDB..MyMainTable
set identity_insert MyMainTable off



    insert MyReferencingTable1 select col1, col2, FKcol+1000....
  from SourceServer.MyDB..MyReferencingTable1

    etc
etc

     


    Cheers,
    - Mark

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

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