Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2002
    Posts
    5

    Question Unanswered: Transferring data from test to live server

    Hi,

    I would like to be able to transfer some data from my test database to the live database (identical schemas).

    The problem I have is that the data has many related tables using automatically generated id numbers as the keys. If I was just to transfer the data using dts it would break the relationships because new id's would be generated. I can't enable the force keep id option because those id's might already be used on the live server.

    How do I transfer the data maintaining the relationships between the tables and not conflicting with existing data on the live server? Do I need to do some sort of merge replication or is this over complicating the issue?

    Thanks in advance for any help!
    Rob

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    If I understood correctly your problem is how to update(change) foreign keys after inserting new rows to live database.
    I guess the simplest way is to create temporary table for saving old keys and new keys for every table.
    Below is just draft how it is possible to do.

    create table t_target(id int identity,code varchar(10))
    go
    create table t_source(id int identity,code varchar(10))
    go
    create table t_tmp(id_old int,id_new)
    go
    insert t_target(code) values('IN')
    insert t_target(code) values('PA')
    insert t_target(code) values('WA')

    insert t_source(code) values('TN')
    insert t_source(code) values('KY')
    insert t_source(code) values('IL')

    insert t_target(code) select code from t_source

    insert t_tmp select s.id,t.id
    from t_source s join t_target t on t.code=s.code

    Table t_tmp keeps old and new keys - you can use it for updating update(change) foreign keys.

    Good luck.

Posting Permissions

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