Dfyjkt

I'm transferring data between two databases.

My old database has a table called Customers, and I have a column called Email in there.

My new database has a new table called Account, that has a relation with the old Customers table.

So, I would like to transfer all the customers from the old database, to the new one. Separate the emails from that table, and Insert into a new one. Example:

My old database table called Customers:

ID, Email, First Name, Last Name

My new database table called Customers:

ID, Account ID, First Name, Last Name

My new database table called Account:

ID, Email, Password

I already transferred the Customers to my new database, and I inserted all the Emails from the customers into the Account table.

But now I would like to update the column Account ID from my new Customers table, to relate all the customers to their account.

INSERT INTO dbo.Account (Email)
 SELECT a.EmailAddress
 FROM AdventureWorks_Test.dbo.Customer a

How can I do that?

Thank you.

The best approach here is to preserve ids for later mapping.You can use a temporary table for that.

CREATE TABLE #MigrationMapper
( OldId INT,
 NewId INT )

Then you can populate this table while inserting the data into the new table. You can make use of OUTPUT clause to get Inserted Id's

MERGE INTO Account AS T 
USING AdventureWorks_Test.dbo.Customer s
ON t.Id=a.Id+ NULL --To insert all records 
WHEN NOT MATCHED THEN 
INSERT ( Email)
VALUES ( s.EmailAddress)
OUTPUT s.Id, INSERTED.ID INTO #MigrationMapper(OldId,NewId); 

So by joining with the temp table now, you know which email address goes with the new customer row