Merge tables and collect origin info

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Merge tables and collect origin info

Author

Topic

laca81
Starting Member

9 Posts

Posted - 2015-04-29 : 03:45:14

Hi guys,

If you have 2 tables with the same columns and you would like to see all distinct records in a result of a select and also the information in the records which table the record comes from (for instance: from table A or from table B or bot tables contain it) what should you do?

Thanks in advance!

jleitao
Posting Yak Master

100 Posts

Posted - 2015-04-29 : 07:42:13

you can use FULL OUTER JOIN to join the tables.
And "create a column" on SQL command to indicate the table.

------------------------
PS - Sorry my bad english

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2015-04-29 : 07:53:14

here's a few ways

DECLARE @Table1 Table ( col1 CHAR(1))
DECLARE @Table2 Table ( col1 CHAR(1))
 
INSERT INTO @Table1 values ('a'),('b'),('C')
INSERT INTO @Table2 values ('e'),('f'),('C')
 
 
 SELECT col1,sum(distinct tbl)
 FROM
 (
    select *,1 as Tbl
    from @table1
    union  all
    select *,2
    from @table2
)     a

group by col1


select *,'In table1 only'
from @table1
except
select *,'In table1 only'
from @table2

union

select *,'In table2 only'
from @table2
except
select *,'In table2 only'
from @table1
union
select *,'In table1 only'
from @table1
except
select *,'In table1 only'
from @table2

union

select *,'In both'
from @table2
intersect
select *,'in both'
from @table1

Jim

Everyday I learn something that somebody else already knew

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2015-04-29 : 07:56:54

when you say distinct records, do you mean records which are matching ?

Hema Sunder

laca81
Starting Member

9 Posts

Posted - 2015-04-29 : 12:00:37

quote:
Originally posted by sunder.bugatha

when you say distinct records, do you mean records which are matching ?

Hema Sunder

Yes.

laca81
Starting Member

9 Posts

Posted - 2015-04-29 : 12:11:56

quote:

Originally posted by jimf

here's a few ways

DECLARE @Table1 Table ( col1 CHAR(1))
DECLARE @Table2 Table ( col1 CHAR(1))
 
INSERT INTO @Table1 values ('a'),('b'),('C')
INSERT INTO @Table2 values ('e'),('f'),('C')
 
 
 SELECT col1,sum(distinct tbl)
 FROM
 (
    select *,1 as Tbl
    from @table1
    union  all
    select *,2
    from @table2
)     a

group by col1


select *,'In table1 only'
from @table1
except
select *,'In table1 only'
from @table2

union

select *,'In table2 only'
from @table2
except
select *,'In table2 only'
from @table1
union
select *,'In table1 only'
from @table1
except
select *,'In table1 only'
from @table2

union

select *,'In both'
from @table2
intersect
select *,'in both'
from @table1

Jim

Everyday I learn something that somebody else already knew

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources