Skip to content
Advertisement

Copy a dependent table

I have this customers table (I’m using SQL Server):

enter image description here

About 300 customers were registered in this table. I created another table in another database and inserted these customers into the new database.

Here is the new customers table:

enter image description here

But I have an operation table as well and I didn’t change this one. The problem is the foreign key here. Since PhoneNumber is no longer the primary key in Customers table, customerId shouldn’t be filled with the phone number anymore. I want to know how can I insert about 1000 operations in the new operation table but use each customer’s ID as a foreign key instead of phone number in customerId.

Here is the operations table:

enter image description here

Advertisement

Answer

If we assume that AFDB is the old database and StoreDB is the new one, the following query helped me out:

    INSERT INTO StoreDB.dbo.Operations
    SELECT  StoreDB.dbo.Customers.Id, TypeID, Amount, [DateTime]
    FROM AFDB.dbo.Operations JOIN StoreDB.dbo.Customers
    ON StoreDb.dbo.Customers.PhoneNumber = AFDB.dbo.Operations.CustomerId;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement