Having issues migrating from an old table to a new one
0
I'm transferring data from one table to another.
The old table used to have a varchar column called City.
The new table instead of the name of the city, has an int column called CityId because I created a new table only for Cities.
My Cities table has the following columns:
CityID - PK
RegionID - FK
CityName - Varchar(50)
I'm running this Query to copy the old data for my new table:
INSERT INTO dbo.Client(EmailAddress, CityID)
a.EmailAddress, (SELECT CityID FROM Cities WHERE CityName collate SQL_Latin1_General_CP1_CI_AS = a.City)
FROM AdventureWorksOld.dbo.Client a
But I can't do that, because the subquery returns multiple rows:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How can I fix my code so I can do something like this:
SELECT (SELECT CityName FROM Cities WHERE CityName = 'Oak Bay') -> Replace 'Oak Bay' with the text in the column
FROM AdventureWorksOld.dbo.Client
Thank you.
sql
sql-server sql-server-2005 sql-server-2012 asked Nov 13 '18 at 23:52
A.BCA.BC
374
add a comment |
0
I'm transferring data from one table to another.
The old table used to have a varchar column called City.
The new table instead of the name of the city, has an int column called CityId because I created a new table only for Cities.
My Cities table has the following columns:
CityID - PK
RegionID - FK
CityName - Varchar(50)
I'm running this Query to copy the old data for my new table:
INSERT INTO dbo.Client(EmailAddress, CityID)
a.EmailAddress, (SELECT CityID FROM Cities WHERE CityName collate SQL_Latin1_General_CP1_CI_AS = a.City)
FROM AdventureWorksOld.dbo.Client a
But I can't do that, because the subquery returns multiple rows:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How can I fix my code so I can do something like this:
SELECT (SELECT CityName FROM Cities WHERE CityName = 'Oak Bay') -> Replace 'Oak Bay' with the text in the column
FROM AdventureWorksOld.dbo.Client
Thank you.
sql
sql-server sql-server-2005 sql-server-2012 asked Nov 13 '18 at 23:52
A.BCA.BC
374
please post your Cities table
– maSTAShuFu
Nov 14 '18 at 0:08
check your data in tableCities. It seems that there are multiple rows ofCityIDfor aCity
– Squirrel
Nov 14 '18 at 0:25
1
RunSELECT CityName, COUNT(*) FROM Cities GROUP BY CityNameto find the records with duplicate city names. Delete one of the duplicates and try again.
– Nick.McDermaid
Nov 14 '18 at 0:38
Thank you, now I know why the query wasn't working, I had duplicated records
– A.BC
Nov 14 '18 at 0:49
add a comment |
0
0
0
I'm transferring data from one table to another.
The old table used to have a varchar column called City.
The new table instead of the name of the city, has an int column called CityId because I created a new table only for Cities.
My Cities table has the following columns:
CityID - PK
RegionID - FK
CityName - Varchar(50)
I'm running this Query to copy the old data for my new table:
INSERT INTO dbo.Client(EmailAddress, CityID)
a.EmailAddress, (SELECT CityID FROM Cities WHERE CityName collate SQL_Latin1_General_CP1_CI_AS = a.City)
FROM AdventureWorksOld.dbo.Client a
But I can't do that, because the subquery returns multiple rows:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How can I fix my code so I can do something like this:
SELECT (SELECT CityName FROM Cities WHERE CityName = 'Oak Bay') -> Replace 'Oak Bay' with the text in the column
FROM AdventureWorksOld.dbo.Client
Thank you.
sql
sql-server sql-server-2005 sql-server-2012 asked Nov 13 '18 at 23:52
A.BCA.BC
374
I'm transferring data from one table to another.
The old table used to have a varchar column called City.
The new table instead of the name of the city, has an int column called CityId because I created a new table only for Cities.
My Cities table has the following columns:
CityID - PK
RegionID - FK
CityName - Varchar(50)
I'm running this Query to copy the old data for my new table:
INSERT INTO dbo.Client(EmailAddress, CityID)
a.EmailAddress, (SELECT CityID FROM Cities WHERE CityName collate SQL_Latin1_General_CP1_CI_AS = a.City)
FROM AdventureWorksOld.dbo.Client a
But I can't do that, because the subquery returns multiple rows:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How can I fix my code so I can do something like this:
SELECT (SELECT CityName FROM Cities WHERE CityName = 'Oak Bay') -> Replace 'Oak Bay' with the text in the column
FROM AdventureWorksOld.dbo.Client
Thank you.
sql
sql-server sql-server-2005 sql-server-2012 sql
sql-server sql-server-2005 sql-server-2012 asked Nov 13 '18 at 23:52
A.BCA.BC
374
asked Nov 13 '18 at 23:52
A.BCA.BC
374
edited Nov 14 '18 at 0:25
A.BC
asked Nov 13 '18 at 23:52
A.BCA.BC
374
asked Nov 13 '18 at 23:52
A.BCA.BC
374
asked Nov 13 '18 at 23:52
A.BCA.BC
374
374
please post your Cities table
– maSTAShuFu
Nov 14 '18 at 0:08
check your data in tableCities. It seems that there are multiple rows ofCityIDfor aCity
– Squirrel
Nov 14 '18 at 0:25
1
RunSELECT CityName, COUNT(*) FROM Cities GROUP BY CityNameto find the records with duplicate city names. Delete one of the duplicates and try again.
– Nick.McDermaid
Nov 14 '18 at 0:38
Thank you, now I know why the query wasn't working, I had duplicated records
– A.BC
Nov 14 '18 at 0:49
add a comment |
please post your Cities table
– maSTAShuFu
Nov 14 '18 at 0:08
check your data in tableCities. It seems that there are multiple rows ofCityIDfor aCity
– Squirrel
Nov 14 '18 at 0:25
1
RunSELECT CityName, COUNT(*) FROM Cities GROUP BY CityNameto find the records with duplicate city names. Delete one of the duplicates and try again.
– Nick.McDermaid
Nov 14 '18 at 0:38
Thank you, now I know why the query wasn't working, I had duplicated records
– A.BC
Nov 14 '18 at 0:49
please post your Cities table
– maSTAShuFu
Nov 14 '18 at 0:08
please post your Cities table
– maSTAShuFu
Nov 14 '18 at 0:08
check your data in table
Cities. It seems that there are multiple rows of CityID for a City– Squirrel
Nov 14 '18 at 0:25
check your data in table
Cities. It seems that there are multiple rows of CityID for a City– Squirrel
Nov 14 '18 at 0:25
1
1
Run
SELECT CityName, COUNT(*) FROM Cities GROUP BY CityName to find the records with duplicate city names. Delete one of the duplicates and try again.– Nick.McDermaid
Nov 14 '18 at 0:38
Run
SELECT CityName, COUNT(*) FROM Cities GROUP BY CityName to find the records with duplicate city names. Delete one of the duplicates and try again.– Nick.McDermaid
Nov 14 '18 at 0:38
Thank you, now I know why the query wasn't working, I had duplicated records
– A.BC
Nov 14 '18 at 0:49
Thank you, now I know why the query wasn't working, I had duplicated records
– A.BC
Nov 14 '18 at 0:49
add a comment |
2 Answers
2
active
oldest
votes
-1
probably this is what are you looking for ?
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
LEFT JOIN Cities c ON c.CityName = a.City
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
This code will fail in the OP's environment because he apparently had duplicate values in his Cities Table.
– Mark Kram
Nov 14 '18 at 1:42
add a comment |
-1
Try this:
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
OUTER APPLY (SELECT TOP 1 c.CityID FROM Cities c WHERE c.CityName = a.City) c
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
this query works but it does not resolve the root cause of the issue which is data issue
– Squirrel
Nov 14 '18 at 1:54
1
That is correct but it solves the issue the OP requested assistance with.@Squirrel. thanks for the down vote.
– Mark Kram
Nov 14 '18 at 1:58
I down vote for 2 reasons. Firstly is the one that I have already mention. Second is a query / code without any explanation or description is not very helpful to others. If you can improve on the answer, i will be happy to retract the down vote
– Squirrel
Nov 14 '18 at 2:28
Well sometime you don't have the ability to make changes to a database and dinnertime you just need to get the job done but you have a great day @Squirrel
– Mark Kram
Nov 14 '18 at 12:13
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f53291171%2fhaving-issues-migrating-from-an-old-table-to-a-new-one%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
-1
probably this is what are you looking for ?
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
LEFT JOIN Cities c ON c.CityName = a.City
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
This code will fail in the OP's environment because he apparently had duplicate values in his Cities Table.
– Mark Kram
Nov 14 '18 at 1:42
add a comment |
-1
probably this is what are you looking for ?
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
LEFT JOIN Cities c ON c.CityName = a.City
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
This code will fail in the OP's environment because he apparently had duplicate values in his Cities Table.
– Mark Kram
Nov 14 '18 at 1:42
add a comment |
-1
-1
-1
probably this is what are you looking for ?
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
LEFT JOIN Cities c ON c.CityName = a.City
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
probably this is what are you looking for ?
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
LEFT JOIN Cities c ON c.CityName = a.City
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
answered Nov 14 '18 at 1:18
iSR5iSR5
1,533278
1,533278
This code will fail in the OP's environment because he apparently had duplicate values in his Cities Table.
– Mark Kram
Nov 14 '18 at 1:42
add a comment |
This code will fail in the OP's environment because he apparently had duplicate values in his Cities Table.
– Mark Kram
Nov 14 '18 at 1:42
This code will fail in the OP's environment because he apparently had duplicate values in his Cities Table.
– Mark Kram
Nov 14 '18 at 1:42
This code will fail in the OP's environment because he apparently had duplicate values in his Cities Table.
– Mark Kram
Nov 14 '18 at 1:42
add a comment |
-1
Try this:
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
OUTER APPLY (SELECT TOP 1 c.CityID FROM Cities c WHERE c.CityName = a.City) c
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
this query works but it does not resolve the root cause of the issue which is data issue
– Squirrel
Nov 14 '18 at 1:54
1
That is correct but it solves the issue the OP requested assistance with.@Squirrel. thanks for the down vote.
– Mark Kram
Nov 14 '18 at 1:58
I down vote for 2 reasons. Firstly is the one that I have already mention. Second is a query / code without any explanation or description is not very helpful to others. If you can improve on the answer, i will be happy to retract the down vote
– Squirrel
Nov 14 '18 at 2:28
Well sometime you don't have the ability to make changes to a database and dinnertime you just need to get the job done but you have a great day @Squirrel
– Mark Kram
Nov 14 '18 at 12:13
add a comment |
-1
Try this:
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
OUTER APPLY (SELECT TOP 1 c.CityID FROM Cities c WHERE c.CityName = a.City) c
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
this query works but it does not resolve the root cause of the issue which is data issue
– Squirrel
Nov 14 '18 at 1:54
1
That is correct but it solves the issue the OP requested assistance with.@Squirrel. thanks for the down vote.
– Mark Kram
Nov 14 '18 at 1:58
I down vote for 2 reasons. Firstly is the one that I have already mention. Second is a query / code without any explanation or description is not very helpful to others. If you can improve on the answer, i will be happy to retract the down vote
– Squirrel
Nov 14 '18 at 2:28
Well sometime you don't have the ability to make changes to a database and dinnertime you just need to get the job done but you have a great day @Squirrel
– Mark Kram
Nov 14 '18 at 12:13
add a comment |
-1
-1
-1
Try this:
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
OUTER APPLY (SELECT TOP 1 c.CityID FROM Cities c WHERE c.CityName = a.City) c
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
Try this:
INSERT INTO dbo.Client(EmailAddress, CityID)
SELECT
a.EmailAddress
, c.CityID
FROM AdventureWorksOld.dbo.Client a
OUTER APPLY (SELECT TOP 1 c.CityID FROM Cities c WHERE c.CityName = a.City) c
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
edited Nov 14 '18 at 1:53
Squirrel
11.8k22127
11.8k22127
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
answered Nov 14 '18 at 1:44
Mark KramMark Kram
4,01043967
4,01043967
this query works but it does not resolve the root cause of the issue which is data issue
– Squirrel
Nov 14 '18 at 1:54
1
That is correct but it solves the issue the OP requested assistance with.@Squirrel. thanks for the down vote.
– Mark Kram
Nov 14 '18 at 1:58
I down vote for 2 reasons. Firstly is the one that I have already mention. Second is a query / code without any explanation or description is not very helpful to others. If you can improve on the answer, i will be happy to retract the down vote
– Squirrel
Nov 14 '18 at 2:28
Well sometime you don't have the ability to make changes to a database and dinnertime you just need to get the job done but you have a great day @Squirrel
– Mark Kram
Nov 14 '18 at 12:13
add a comment |
this query works but it does not resolve the root cause of the issue which is data issue
– Squirrel
Nov 14 '18 at 1:54
1
That is correct but it solves the issue the OP requested assistance with.@Squirrel. thanks for the down vote.
– Mark Kram
Nov 14 '18 at 1:58
I down vote for 2 reasons. Firstly is the one that I have already mention. Second is a query / code without any explanation or description is not very helpful to others. If you can improve on the answer, i will be happy to retract the down vote
– Squirrel
Nov 14 '18 at 2:28
Well sometime you don't have the ability to make changes to a database and dinnertime you just need to get the job done but you have a great day @Squirrel
– Mark Kram
Nov 14 '18 at 12:13
this query works but it does not resolve the root cause of the issue which is data issue
– Squirrel
Nov 14 '18 at 1:54
this query works but it does not resolve the root cause of the issue which is data issue
– Squirrel
Nov 14 '18 at 1:54
1
1
That is correct but it solves the issue the OP requested assistance with.@Squirrel. thanks for the down vote.
– Mark Kram
Nov 14 '18 at 1:58
That is correct but it solves the issue the OP requested assistance with.@Squirrel. thanks for the down vote.
– Mark Kram
Nov 14 '18 at 1:58
I down vote for 2 reasons. Firstly is the one that I have already mention. Second is a query / code without any explanation or description is not very helpful to others. If you can improve on the answer, i will be happy to retract the down vote
– Squirrel
Nov 14 '18 at 2:28
I down vote for 2 reasons. Firstly is the one that I have already mention. Second is a query / code without any explanation or description is not very helpful to others. If you can improve on the answer, i will be happy to retract the down vote
– Squirrel
Nov 14 '18 at 2:28
Well sometime you don't have the ability to make changes to a database and dinnertime you just need to get the job done but you have a great day @Squirrel
– Mark Kram
Nov 14 '18 at 12:13
Well sometime you don't have the ability to make changes to a database and dinnertime you just need to get the job done but you have a great day @Squirrel
– Mark Kram
Nov 14 '18 at 12:13
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53291171%2fhaving-issues-migrating-from-an-old-table-to-a-new-one%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
please post your Cities table
– maSTAShuFu
Nov 14 '18 at 0:08
check your data in table
Cities. It seems that there are multiple rows ofCityIDfor aCity– Squirrel
Nov 14 '18 at 0:25
1
Run
SELECT CityName, COUNT(*) FROM Cities GROUP BY CityNameto find the records with duplicate city names. Delete one of the duplicates and try again.– Nick.McDermaid
Nov 14 '18 at 0:38
Thank you, now I know why the query wasn't working, I had duplicated records
– A.BC
Nov 14 '18 at 0:49