How to compare two recordset ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to compare two recordset ?

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2008-10-17 : 14:55:43

Hi All,

We create two record set one from database and another one from Excel sheet.

Both tables should be exactly same. (All rows and columns)

Is there anyway using SQL query I can compare both tables and return True all cells are same if they all are same and return False ?

Thanks,
Shahid

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-17 : 15:43:51

Don't really understand what you are trying to accomplish. Are you comparing an excel worksheet with a table in the database? Please specify.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-17 : 16:36:15

[code]
-- Bulk Load the Excel into a temp table with the same structure.
-- Then do the compare using the primary key to join the tables

-- This is your original
create table #DB (
pk int,
c1 varchar(50),
c2 varchar(50),
c3 varchar(50)
)

insert into #DB
select 1, 'Hello', 'World', 1 union all
select 2, 'Hello', 'My World', 2 union all
select 3, 'Hello', 'Your World', 3 union all
select 4, 'Hello', 'A World', 4 union all
select 5, 'Hello', 'Some World', 5

-- This is your XLS
create table #XLS (
pk int,
c1 varchar(50),
c2 varchar(50),
c3 varchar(50)
)

insert into #XLS
select 1, 'Hello', 'World', 2 union all -- Different
select 2, 'Hello', 'My World', 2 union all
select 3, 'Hello', 'Your own World', 3 union all -- different
select 4, 'Hello', 'A World', 4 union all
select 5, 'Hello', 'Some World', 5

-- select * from #DB
-- select * from #XLS

select * from (
select #DB.*, 'SAME' compare from #DB left JOIN #XLS on #DB.pk=#XLS.PK
WHERE #DB.c1=#XLS.c1 AND #DB.c2=#XLS.c2 AND #DB.c3=#XLS.c3
union all
select #DB.*, 'DIFFERENT' compare from #DB left JOIN #XLS on #DB.pk=#XLS.PK
WHERE (#DB.c1<>#XLS.c1 OR #DB.c2<>#XLS.c2 OR #DB.c3<>#XLS.c3)
) a ORDER BY a.pk

drop table #DB
drop table #XLS
[/code]

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources