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.
sql-server sql-server-2012 edited Nov 13 '18 at 6:25
marc_s
571k12811031252
asked Nov 13 '18 at 3:08
A.BC
324
add a comment |
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.
sql-server sql-server-2012 edited Nov 13 '18 at 6:25
marc_s
571k12811031252
asked Nov 13 '18 at 3:08
A.BC
324
1
In what way does the new Customers "has a relation with the old Customers table."? Have you preserved the old id in the new Customers table? How many records in the old Customers table?
– Nick.McDermaid
Nov 13 '18 at 3:16
1
Now that you've separated them, how do you know which email address goes with the new account row? You broke the connection between the tables by importing them separately. How do you connect them back together again?
– Ken White
Nov 13 '18 at 3:33
add a comment |
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.
sql-server sql-server-2012 edited Nov 13 '18 at 6:25
marc_s
571k12811031252
asked Nov 13 '18 at 3:08
A.BC
324
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.
sql-server sql-server-2012 sql-server sql-server-2012 edited Nov 13 '18 at 6:25
marc_s
571k12811031252
asked Nov 13 '18 at 3:08
A.BC
324
edited Nov 13 '18 at 6:25
marc_s
571k12811031252
asked Nov 13 '18 at 3:08
A.BC
324
edited Nov 13 '18 at 6:25
marc_s
571k12811031252
edited Nov 13 '18 at 6:25
marc_s
571k12811031252
edited Nov 13 '18 at 6:25
marc_s
571k12811031252
571k12811031252
asked Nov 13 '18 at 3:08
A.BC
324
asked Nov 13 '18 at 3:08
A.BC
324
asked Nov 13 '18 at 3:08
A.BC
324
324
1
In what way does the new Customers "has a relation with the old Customers table."? Have you preserved the old id in the new Customers table? How many records in the old Customers table?
– Nick.McDermaid
Nov 13 '18 at 3:16
1
Now that you've separated them, how do you know which email address goes with the new account row? You broke the connection between the tables by importing them separately. How do you connect them back together again?
– Ken White
Nov 13 '18 at 3:33
add a comment |
1
In what way does the new Customers "has a relation with the old Customers table."? Have you preserved the old id in the new Customers table? How many records in the old Customers table?
– Nick.McDermaid
Nov 13 '18 at 3:16
1
Now that you've separated them, how do you know which email address goes with the new account row? You broke the connection between the tables by importing them separately. How do you connect them back together again?
– Ken White
Nov 13 '18 at 3:33
1
1
In what way does the new Customers "has a relation with the old Customers table."? Have you preserved the old id in the new Customers table? How many records in the old Customers table?
– Nick.McDermaid
Nov 13 '18 at 3:16
In what way does the new Customers "has a relation with the old Customers table."? Have you preserved the old id in the new Customers table? How many records in the old Customers table?
– Nick.McDermaid
Nov 13 '18 at 3:16
1
1
Now that you've separated them, how do you know which email address goes with the new account row? You broke the connection between the tables by importing them separately. How do you connect them back together again?
– Ken White
Nov 13 '18 at 3:33
Now that you've separated them, how do you know which email address goes with the new account row? You broke the connection between the tables by importing them separately. How do you connect them back together again?
– Ken White
Nov 13 '18 at 3:33
add a comment |
1 Answer
1
active
oldest
votes
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
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
I'm receiving this error: The multi-part identifier could not be bound, the ID from the customer is not being recognized
– A.BC
Nov 13 '18 at 10:38
might be aliasing problem can please create a dbfiddle and share the link
– Sanal Sunny
Nov 13 '18 at 10:51
I found the solution. Its not possible to insert the ID in that way: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– A.BC
Nov 13 '18 at 10:54
I'm retreiving the INSERTED.EmailAddress instead of retreiving the ID from the customers table
– A.BC
Nov 13 '18 at 10:54
@A.BC now i know the error thanks for pointing out . We can use Merge statement for insert so that we can get the customer id's
– Sanal Sunny
Nov 13 '18 at 10:56
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273204%2finsert-and-update-two-tables-at-the-same-time%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
I'm receiving this error: The multi-part identifier could not be bound, the ID from the customer is not being recognized
– A.BC
Nov 13 '18 at 10:38
might be aliasing problem can please create a dbfiddle and share the link
– Sanal Sunny
Nov 13 '18 at 10:51
I found the solution. Its not possible to insert the ID in that way: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– A.BC
Nov 13 '18 at 10:54
I'm retreiving the INSERTED.EmailAddress instead of retreiving the ID from the customers table
– A.BC
Nov 13 '18 at 10:54
@A.BC now i know the error thanks for pointing out . We can use Merge statement for insert so that we can get the customer id's
– Sanal Sunny
Nov 13 '18 at 10:56
|
show 2 more comments
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
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
I'm receiving this error: The multi-part identifier could not be bound, the ID from the customer is not being recognized
– A.BC
Nov 13 '18 at 10:38
might be aliasing problem can please create a dbfiddle and share the link
– Sanal Sunny
Nov 13 '18 at 10:51
I found the solution. Its not possible to insert the ID in that way: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– A.BC
Nov 13 '18 at 10:54
I'm retreiving the INSERTED.EmailAddress instead of retreiving the ID from the customers table
– A.BC
Nov 13 '18 at 10:54
@A.BC now i know the error thanks for pointing out . We can use Merge statement for insert so that we can get the customer id's
– Sanal Sunny
Nov 13 '18 at 10:56
|
show 2 more comments
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
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
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
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
edited Nov 13 '18 at 11:03
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
answered Nov 13 '18 at 4:11
Sanal Sunny
6628
6628
I'm receiving this error: The multi-part identifier could not be bound, the ID from the customer is not being recognized
– A.BC
Nov 13 '18 at 10:38
might be aliasing problem can please create a dbfiddle and share the link
– Sanal Sunny
Nov 13 '18 at 10:51
I found the solution. Its not possible to insert the ID in that way: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– A.BC
Nov 13 '18 at 10:54
I'm retreiving the INSERTED.EmailAddress instead of retreiving the ID from the customers table
– A.BC
Nov 13 '18 at 10:54
@A.BC now i know the error thanks for pointing out . We can use Merge statement for insert so that we can get the customer id's
– Sanal Sunny
Nov 13 '18 at 10:56
|
show 2 more comments
I'm receiving this error: The multi-part identifier could not be bound, the ID from the customer is not being recognized
– A.BC
Nov 13 '18 at 10:38
might be aliasing problem can please create a dbfiddle and share the link
– Sanal Sunny
Nov 13 '18 at 10:51
I found the solution. Its not possible to insert the ID in that way: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– A.BC
Nov 13 '18 at 10:54
I'm retreiving the INSERTED.EmailAddress instead of retreiving the ID from the customers table
– A.BC
Nov 13 '18 at 10:54
@A.BC now i know the error thanks for pointing out . We can use Merge statement for insert so that we can get the customer id's
– Sanal Sunny
Nov 13 '18 at 10:56
I'm receiving this error: The multi-part identifier could not be bound, the ID from the customer is not being recognized
– A.BC
Nov 13 '18 at 10:38
I'm receiving this error: The multi-part identifier could not be bound, the ID from the customer is not being recognized
– A.BC
Nov 13 '18 at 10:38
might be aliasing problem can please create a dbfiddle and share the link
– Sanal Sunny
Nov 13 '18 at 10:51
might be aliasing problem can please create a dbfiddle and share the link
– Sanal Sunny
Nov 13 '18 at 10:51
I found the solution. Its not possible to insert the ID in that way: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– A.BC
Nov 13 '18 at 10:54
I found the solution. Its not possible to insert the ID in that way: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
– A.BC
Nov 13 '18 at 10:54
I'm retreiving the INSERTED.EmailAddress instead of retreiving the ID from the customers table
– A.BC
Nov 13 '18 at 10:54
I'm retreiving the INSERTED.EmailAddress instead of retreiving the ID from the customers table
– A.BC
Nov 13 '18 at 10:54
@A.BC now i know the error thanks for pointing out . We can use Merge statement for insert so that we can get the customer id's
– Sanal Sunny
Nov 13 '18 at 10:56
@A.BC now i know the error thanks for pointing out . We can use Merge statement for insert so that we can get the customer id's
– Sanal Sunny
Nov 13 '18 at 10:56
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53273204%2finsert-and-update-two-tables-at-the-same-time%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
In what way does the new Customers "has a relation with the old Customers table."? Have you preserved the old id in the new Customers table? How many records in the old Customers table?
– Nick.McDermaid
Nov 13 '18 at 3:16
1
Now that you've separated them, how do you know which email address goes with the new account row? You broke the connection between the tables by importing them separately. How do you connect them back together again?
– Ken White
Nov 13 '18 at 3:33