I have this customers table (I’m using SQL Server):
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:
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:
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;