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.
| Author |
Topic |
|
figmo
Starting Member
18 Posts |
 Posted - 2009-02-26 : 00:21:24
|
| I found Jeff's excellent article about using UNION ALL to compare 2 tables. http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspxThis is 'almost' exactly what I need. Is it possible to modify this script to display the differences on a single row? No, not every difference on 1 row. What I mean is that currently this script outputs data like this...'Table1', 'Col1', 'Col2'...'Table2', 'Col1', 'Col2'...'Table1', 'Col1', 'Col2'...'Table2', 'Col1', 'Col2'......Each pair of rows represents a difference between the two tables. With the 1st in the pair showing the data for Table1 and the 2nd in the pair showing the data for Table2. What I'd like is the same data, but looking more like this...'Table1.Col1', 'Table1.Col2', 'Table2.Col1', 'Table2.Col2'......So each individual difference is on a single row that contains the column data from both tables.Has anybody modified this script to do this? Is it horribly difficult? |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-26 : 01:04:26
|
| try like thisso sample datadeclare @tab table (id int, ename varchar(32))insert into @tab select 1,'bklr'insert into @tab select 2,'ramu'insert into @tab select 3,'hari'insert into @tab select 4,'kumar'insert into @tab select 5,'chiru'declare @tab1 table (id int, ename varchar(32))insert into @tab1 select 1,'bklr'insert into @tab1 select 2,'nag'insert into @tab1 select 3,'jaya'insert into @tab1 select 4,'kumar'insert into @tab1 select 5,'chiru'select * from @tab except select * from @tab1select * from @tab intersect select * from @tab1select * from @tab union all select * from @tab1 |
 |
|
|
figmo
Starting Member
18 Posts |
Posted - 2009-02-26 : 09:38:45
|
| bklr,That did not seem to work for me. That gives me 3 result sets. What I need is a single set of rows containing the differences between the two tables. Using the sample data you provided, something like this....tab.id tab1.id tab.ename tab1.ename------ ------- --------- ----------2 2 ramu nag3 3 han jaya |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-27 : 01:05:28
|
| try this it will works when the id are same onlyselect ISNULL(t.id ,e.id)as id, MAX(t.ename) , MAX(e.ename) from @tab1 tfull outer join @tab e on t.ename = e.enamewhere (e.ename is null or t.ename is null)group by ISNULL(t.id ,e.id) |
|
|
|
|
|
|
|
|
Compare 2 tables question