Query to detect existence of same data

[katbear]

Hi,

Basically, I want a query to detect if the data in TableA is the *exact same data* as in TableB, row for row, column for column.

Help, is there a way to do this using SQL?

Thank you

 

[bborissov]

And no Primary keys?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[katbear]

There can be if necessary

 

[gmmastros]

Interesting question. There is a checksum function that can help out here. Take a look at the query below. Play around with the data in the 2 table variables to give yourself a warm fuzzy that this query will actually work for you.

[COLOR=blue]Declare[/color] @Table1 [COLOR=blue]Table[/color](Col1 [COLOR=blue]int[/color], col2 [COLOR=blue]varchar[/color](20))
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table1 [COLOR=blue]Values[/color](1, [COLOR=red]'orange'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table1 [COLOR=blue]Values[/color](2, [COLOR=red]'purple'[/color])
[COLOR=green]--Insert Into @Table1 Values(4, 'blue')
[/color]
[COLOR=blue]Declare[/color] @Table2 [COLOR=blue]Table[/color](Col1 [COLOR=blue]int[/color], col2 [COLOR=blue]varchar[/color](20))
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table2 [COLOR=blue]Values[/color](1, [COLOR=red]'orange'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Table2 [COLOR=blue]Values[/color](2, [COLOR=red]'purple'[/color])
[COLOR=green]--Insert Into @Table2 Values(3, 'green')
[/color]
[COLOR=blue]If[/color] Exists(
          [COLOR=blue]Select[/color] *
          [COLOR=blue]From[/color]   (
                 [COLOR=blue]Select[/color] checksum(*) [COLOR=blue]As[/color] A
                 [COLOR=blue]From[/color]   @Table1
                 ) [COLOR=blue]As[/color] T1
                 [COLOR=blue]Full[/color] [COLOR=blue]Join[/color] (
                     [COLOR=blue]Select[/color] Checksum(*) [COLOR=blue]As[/color] A
                     [COLOR=blue]From[/color]   @Table2
                     ) [COLOR=blue]As[/color] T2
                   [COLOR=blue]On[/color] T1.A = T2.A
                 [COLOR=blue]Where[/color] T1.A [COLOR=blue]Is[/color] NULL Or T2.A [COLOR=blue]Is[/color] NULL
         )  
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Select[/color] [COLOR=red]'Tables Are Different'[/color]
  [COLOR=blue]End[/color]
[COLOR=blue]Else[/color]
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Select[/color] [COLOR=red]'Tables are the same'[/color]
  [COLOR=blue]End[/color]

-George

"the screen with the little boxes in the window." - Moron

 

[bborissov]

If there are PK in both tables that match each other then compare tables with that keys, the rest if th columns MUST be the same.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

select * from (
   select * from tableA
   union all select * from tableB
) X
group by {allcolumns}
having count(*) = 1

Replace {allcolumns} with the list of all columns in the two tables.

or, if you have a primary key:

select * from (
   select * from tableA
   union select * from tableB
) X
group by {primarykey}
having count(*) = 2

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]