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 |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2011-06-14 : 01:00:03
|
| Dear All,I have 3 tables having some 10 fields of each i need to compare the each column with another 4th table to check whether these records are exist or not in these 3 tables.We don't have any primary key as in the 4th table to match to make join.Let me know how do we do this,Thanks,GangadharThanks,Gangadhara MSSQL Developer and DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fnirp
Starting Member
2 Posts |
Posted - 2011-06-16 : 09:13:33
|
| If the tables has identical columns, I probably would do something like this to compare two tables with four columns. The resultset is all the rows that don't match.select col1, col2, col3, col4, count(*) as c1, 'tablename' as t1 into #tmpfrom table_namegroup by col1, col2, col3, col4insert #tmp select col1, col2, col3, col4, count(*) as c1, 'mastertable' as t1 from master_table group by col1, col2, col3, col4select col1, col2, col3, col4, c1, t1from #tmp t1join (select col1, col2, col3, col4, c1 from #tmp group by col1, col2, col3, col4, c1 having count(*) <> 2) t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3 and t1.col4 = t2.col4order by col1, col2, col3, col4, c1//Fnirp |
 |
|
|
|
|
|
|
|
row comparasion