CODEWITHSUNDEEP
sql-serversql-server-2012
A.BC
A.BC

Reputation: 79

Insert and update two tables at the same time

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.

Upvotes: 0

Views: 1300

Answers (1)

Sanal Sunny
Sanal Sunny

Reputation: 617

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

Upvotes: 2

Related Questions