Mixing
Comparing all columns in two different tables
0
I have two tables that I need to join together. Once joined I want to compare 50 different columns and list every row where the columns don't match. Is there an easier way to solve this than by doing
SELECT *
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON
TABLE1.ID = TABLE2.ID
WHERE T1.NAME <> T2.NAME
OR T1.DESCRIPTION <> T2.DESCRIPTON --48 more comparisons
sql-server edited Nov 20 '18 at 20:24
scsimon
22k51536
asked Nov 20 '18 at 20:22
MattCMattC
57111
|
show 1 more comment
0
I have two tables that I need to join together. Once joined I want to compare 50 different columns and list every row where the columns don't match. Is there an easier way to solve this than by doing
SELECT *
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON
TABLE1.ID = TABLE2.ID
WHERE T1.NAME <> T2.NAME
OR T1.DESCRIPTION <> T2.DESCRIPTON --48 more comparisons
edited Nov 20 '18 at 20:24
scsimon
22k51536
asked Nov 20 '18 at 20:22
MattCMattC
57111
are the table's identical in regards to column names (and order)? If so, you can useEXCEPT
– scsimon
Nov 20 '18 at 20:24
No, but if it would help I could create a view for the 2nd table to appear the same as the 1st.
– MattC
Nov 20 '18 at 20:26
Creating the view wouldn't save you any extra keystrokes from just adding the 48 other comparisons i wouldn't think. Maybe someone has a crafty way. Are you just trying to mitigate keystrokes or is this performing terribly given all the predicates?
– scsimon
Nov 20 '18 at 20:28
I am just trying save keystrokes now and prevent someone from forgetting to add any new fields to the comparison section in the future.
– MattC
Nov 20 '18 at 20:38
extra comparisons would only happen when new columns are added (DDL changes) in which you'd have to update your view as well. I don't have a good way unfortunately.
– scsimon
Nov 20 '18 at 20:40
|
show 1 more comment
0
0
0
I have two tables that I need to join together. Once joined I want to compare 50 different columns and list every row where the columns don't match. Is there an easier way to solve this than by doing
SELECT *
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON
TABLE1.ID = TABLE2.ID
WHERE T1.NAME <> T2.NAME
OR T1.DESCRIPTION <> T2.DESCRIPTON --48 more comparisons
edited Nov 20 '18 at 20:24
scsimon
22k51536
asked Nov 20 '18 at 20:22
MattCMattC
57111
I have two tables that I need to join together. Once joined I want to compare 50 different columns and list every row where the columns don't match. Is there an easier way to solve this than by doing
SELECT *
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON
TABLE1.ID = TABLE2.ID
WHERE T1.NAME <> T2.NAME
OR T1.DESCRIPTION <> T2.DESCRIPTON --48 more comparisons
edited Nov 20 '18 at 20:24
scsimon
22k51536
asked Nov 20 '18 at 20:22
MattCMattC
57111
edited Nov 20 '18 at 20:24
scsimon
22k51536
asked Nov 20 '18 at 20:22
MattCMattC
57111
edited Nov 20 '18 at 20:24
scsimon
22k51536
edited Nov 20 '18 at 20:24
scsimon
22k51536
edited Nov 20 '18 at 20:24
scsimon
22k51536
22k51536
asked Nov 20 '18 at 20:22
MattCMattC
57111
asked Nov 20 '18 at 20:22
MattCMattC
57111
asked Nov 20 '18 at 20:22
MattCMattC
57111
57111
are the table's identical in regards to column names (and order)? If so, you can useEXCEPT
– scsimon
Nov 20 '18 at 20:24
No, but if it would help I could create a view for the 2nd table to appear the same as the 1st.
– MattC
Nov 20 '18 at 20:26
Creating the view wouldn't save you any extra keystrokes from just adding the 48 other comparisons i wouldn't think. Maybe someone has a crafty way. Are you just trying to mitigate keystrokes or is this performing terribly given all the predicates?
– scsimon
Nov 20 '18 at 20:28
I am just trying save keystrokes now and prevent someone from forgetting to add any new fields to the comparison section in the future.
– MattC
Nov 20 '18 at 20:38
extra comparisons would only happen when new columns are added (DDL changes) in which you'd have to update your view as well. I don't have a good way unfortunately.
– scsimon
Nov 20 '18 at 20:40
|
show 1 more comment
are the table's identical in regards to column names (and order)? If so, you can useEXCEPT
– scsimon
Nov 20 '18 at 20:24
No, but if it would help I could create a view for the 2nd table to appear the same as the 1st.
– MattC
Nov 20 '18 at 20:26
Creating the view wouldn't save you any extra keystrokes from just adding the 48 other comparisons i wouldn't think. Maybe someone has a crafty way. Are you just trying to mitigate keystrokes or is this performing terribly given all the predicates?
– scsimon
Nov 20 '18 at 20:28
I am just trying save keystrokes now and prevent someone from forgetting to add any new fields to the comparison section in the future.
– MattC
Nov 20 '18 at 20:38
extra comparisons would only happen when new columns are added (DDL changes) in which you'd have to update your view as well. I don't have a good way unfortunately.
– scsimon
Nov 20 '18 at 20:40
are the table's identical in regards to column names (and order)? If so, you can use
EXCEPT– scsimon
Nov 20 '18 at 20:24
are the table's identical in regards to column names (and order)? If so, you can use
EXCEPT– scsimon
Nov 20 '18 at 20:24
No, but if it would help I could create a view for the 2nd table to appear the same as the 1st.
– MattC
Nov 20 '18 at 20:26
No, but if it would help I could create a view for the 2nd table to appear the same as the 1st.
– MattC
Nov 20 '18 at 20:26
Creating the view wouldn't save you any extra keystrokes from just adding the 48 other comparisons i wouldn't think. Maybe someone has a crafty way. Are you just trying to mitigate keystrokes or is this performing terribly given all the predicates?
– scsimon
Nov 20 '18 at 20:28
Creating the view wouldn't save you any extra keystrokes from just adding the 48 other comparisons i wouldn't think. Maybe someone has a crafty way. Are you just trying to mitigate keystrokes or is this performing terribly given all the predicates?
– scsimon
Nov 20 '18 at 20:28
I am just trying save keystrokes now and prevent someone from forgetting to add any new fields to the comparison section in the future.
– MattC
Nov 20 '18 at 20:38
I am just trying save keystrokes now and prevent someone from forgetting to add any new fields to the comparison section in the future.
– MattC
Nov 20 '18 at 20:38
extra comparisons would only happen when new columns are added (DDL changes) in which you'd have to update your view as well. I don't have a good way unfortunately.
– scsimon
Nov 20 '18 at 20:40
extra comparisons would only happen when new columns are added (DDL changes) in which you'd have to update your view as well. I don't have a good way unfortunately.
– scsimon
Nov 20 '18 at 20:40
|
show 1 more comment
2 Answers
2
active
oldest
votes
0
I don't think there's an elegant way to do this but you could compare the table schemas to one another and inner-join on the column names to generate a list of columns shared by both tables. Something like this:
SELECT Table1.COLUMN_NAME FROM [Database].INFORMATION_SCHEMA.COLUMNS Table1
INNER JOIN [Database].INFORMATION_SCHEMA.COLUMNS Table2
ON Table1.COLUMN_NAME = Table2.COLUMN_NAME
WHERE Table1.TABLE_NAME = N'Table1' AND Table2.TABLE_NAME = N'Table2'
Then write a dynamic query that uses that list of column names for comparison.
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
add a comment |
0
You can use UNION.
--sample tables
WITH tb1 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 4 as b),
tb2 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 5 as b),
--
tablesunion as
(select * from tb1 union
select * from tb2),
tableseq as
(SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS SEQ,
*
FROM tablesunion)
SELECT tb1.*, tb2.*
FROM tableseq s
inner join tb1
on (s.id = tb1.id)
inner join tb2
on (s.id = tb2.id)
WHERE seq > 1;
The result would be:
id a b id a b
112 3 4 112 3 5
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
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%2f53400951%2fcomparing-all-columns-in-two-different-tables%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
0
I don't think there's an elegant way to do this but you could compare the table schemas to one another and inner-join on the column names to generate a list of columns shared by both tables. Something like this:
SELECT Table1.COLUMN_NAME FROM [Database].INFORMATION_SCHEMA.COLUMNS Table1
INNER JOIN [Database].INFORMATION_SCHEMA.COLUMNS Table2
ON Table1.COLUMN_NAME = Table2.COLUMN_NAME
WHERE Table1.TABLE_NAME = N'Table1' AND Table2.TABLE_NAME = N'Table2'
Then write a dynamic query that uses that list of column names for comparison.
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
add a comment |
0
I don't think there's an elegant way to do this but you could compare the table schemas to one another and inner-join on the column names to generate a list of columns shared by both tables. Something like this:
SELECT Table1.COLUMN_NAME FROM [Database].INFORMATION_SCHEMA.COLUMNS Table1
INNER JOIN [Database].INFORMATION_SCHEMA.COLUMNS Table2
ON Table1.COLUMN_NAME = Table2.COLUMN_NAME
WHERE Table1.TABLE_NAME = N'Table1' AND Table2.TABLE_NAME = N'Table2'
Then write a dynamic query that uses that list of column names for comparison.
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
add a comment |
0
0
0
I don't think there's an elegant way to do this but you could compare the table schemas to one another and inner-join on the column names to generate a list of columns shared by both tables. Something like this:
SELECT Table1.COLUMN_NAME FROM [Database].INFORMATION_SCHEMA.COLUMNS Table1
INNER JOIN [Database].INFORMATION_SCHEMA.COLUMNS Table2
ON Table1.COLUMN_NAME = Table2.COLUMN_NAME
WHERE Table1.TABLE_NAME = N'Table1' AND Table2.TABLE_NAME = N'Table2'
Then write a dynamic query that uses that list of column names for comparison.
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
I don't think there's an elegant way to do this but you could compare the table schemas to one another and inner-join on the column names to generate a list of columns shared by both tables. Something like this:
SELECT Table1.COLUMN_NAME FROM [Database].INFORMATION_SCHEMA.COLUMNS Table1
INNER JOIN [Database].INFORMATION_SCHEMA.COLUMNS Table2
ON Table1.COLUMN_NAME = Table2.COLUMN_NAME
WHERE Table1.TABLE_NAME = N'Table1' AND Table2.TABLE_NAME = N'Table2'
Then write a dynamic query that uses that list of column names for comparison.
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
answered Nov 20 '18 at 22:40
Nathan ChampionNathan Champion
702315
702315
add a comment |
add a comment |
0
You can use UNION.
--sample tables
WITH tb1 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 4 as b),
tb2 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 5 as b),
--
tablesunion as
(select * from tb1 union
select * from tb2),
tableseq as
(SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS SEQ,
*
FROM tablesunion)
SELECT tb1.*, tb2.*
FROM tableseq s
inner join tb1
on (s.id = tb1.id)
inner join tb2
on (s.id = tb2.id)
WHERE seq > 1;
The result would be:
id a b id a b
112 3 4 112 3 5
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
add a comment |
0
You can use UNION.
--sample tables
WITH tb1 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 4 as b),
tb2 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 5 as b),
--
tablesunion as
(select * from tb1 union
select * from tb2),
tableseq as
(SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS SEQ,
*
FROM tablesunion)
SELECT tb1.*, tb2.*
FROM tableseq s
inner join tb1
on (s.id = tb1.id)
inner join tb2
on (s.id = tb2.id)
WHERE seq > 1;
The result would be:
id a b id a b
112 3 4 112 3 5
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
add a comment |
0
0
0
You can use UNION.
--sample tables
WITH tb1 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 4 as b),
tb2 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 5 as b),
--
tablesunion as
(select * from tb1 union
select * from tb2),
tableseq as
(SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS SEQ,
*
FROM tablesunion)
SELECT tb1.*, tb2.*
FROM tableseq s
inner join tb1
on (s.id = tb1.id)
inner join tb2
on (s.id = tb2.id)
WHERE seq > 1;
The result would be:
id a b id a b
112 3 4 112 3 5
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
You can use UNION.
--sample tables
WITH tb1 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 4 as b),
tb2 as
(select 111 as id, 1 as a, 2 as b union all select 112 as id, 3 as a, 5 as b),
--
tablesunion as
(select * from tb1 union
select * from tb2),
tableseq as
(SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS SEQ,
*
FROM tablesunion)
SELECT tb1.*, tb2.*
FROM tableseq s
inner join tb1
on (s.id = tb1.id)
inner join tb2
on (s.id = tb2.id)
WHERE seq > 1;
The result would be:
id a b id a b
112 3 4 112 3 5
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
edited Nov 20 '18 at 23:16
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
answered Nov 20 '18 at 23:10
Erika Madeiros SilvaErika Madeiros Silva
163
163
add a comment |
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%2f53400951%2fcomparing-all-columns-in-two-different-tables%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
are the table's identical in regards to column names (and order)? If so, you can use
EXCEPT– scsimon
Nov 20 '18 at 20:24
No, but if it would help I could create a view for the 2nd table to appear the same as the 1st.
– MattC
Nov 20 '18 at 20:26
Creating the view wouldn't save you any extra keystrokes from just adding the 48 other comparisons i wouldn't think. Maybe someone has a crafty way. Are you just trying to mitigate keystrokes or is this performing terribly given all the predicates?
– scsimon
Nov 20 '18 at 20:28
I am just trying save keystrokes now and prevent someone from forgetting to add any new fields to the comparison section in the future.
– MattC
Nov 20 '18 at 20:38
extra comparisons would only happen when new columns are added (DDL changes) in which you'd have to update your view as well. I don't have a good way unfortunately.
– scsimon
Nov 20 '18 at 20:40