Copy from Database A to Database B

  • Hi. I would like to be able to copy data from specified tables in DataBase A to identical Tables in Database B where relationships exist between other tables.....let me explain.......the code below copies data between identical tables from Database A to Database B

    INSERT INTO DatabaseB.dbo.Table1 (Column1,Column2,Column3)

    SELECT Column1,Column2,Column3

    FROM DatabaseB.dbo.Table1

    My problem. The PK field value differs in table A to the FK value in table B. Now I want to copy data from DatabaseB.dbo.Table2 to DatabaseB.dbo.Table2.

    Database A:

    Table A has Column 1 (PK) value '4' / Column 2 'Bob Smith'

    Database B:

    Table A has Column 1 (FK) value '4' / Column 2 'Dave Brown'

    Column A Is Identity Yes Increment 1 Identity Seed 1

    Basically after I run the insert query the FK value in the table within Database B differs to the PK value in the table within Database A, which will affect values if I attempt to copy data to related tables.

    Hope that makes sense as it confuses me!

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • I do not think I can do what I want to do! Well not easily and without a lot of thought.....

    Basically I am working on a non-production database. I want to migrate/copy data from only certain tables to a new database to save having to replicate everything in those tables.

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

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

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