November 18, 2010 at 4:16 am
Hi all,
I am trying to compare four columns in two tables(both are in the same schema), table a has four columns for chartnumber, Dataofservices,CPT and Amount , table b has similar columns chartnumber, Dataofservices,CPT and Amount , i wish to compare both and if there are similarities, do nothing but if there are differencies either of table , export to table. Not quite sure what if statement I should write or how to go about this ...any help would be appreciated.
November 18, 2010 at 4:37 am
the EXCEPT operator is awesome for this, but you can do this with left outer joins as well.
SELECT chartnumber, Dataofservices,CPT,Amount FROM TABLE1
EXCEPT
SELECT chartnumber, Dataofservices,CPT,Amount FROM TABLE2
that will give you lists of what is in one table but not the other.
now if the first three columns are supposed to match, but it's the amount that you are worried about, then you want to left join.
explain a bit more about what the differences should be that you are looking for?
SELECT TABLE1.chartnumber, TABLE1.Dataofservices,TABLE1.CPT,TABLE1.Amount ,
TABLE2.chartnumber, TABLE2.Dataofservices,TABLE2.CPT,TABLE2.Amount
FROM TABLE1
LEFT OUTER JOIN TABLE2
ON TABLE1.chartnumber = TABLE2.chartnumber
AND TABLE1.Dataofservices = TABLE2.Dataofservices
AND TABLE1.CPT = TABLE2.CPT
WHERE TABLE1.Amount <> TABLE2.Amount
Lowell
November 18, 2010 at 4:45 am
SELECT chartnumber, Dataofservices,CPT,Amount FROM TABLE1
union
SELECT chartnumber, Dataofservices,CPT,Amount FROM TABLE2
EXCEPT
SELECT chartnumber, Dataofservices,CPT,Amount FROM TABLE1
INTERSECT
SELECT chartnumber, Dataofservices,CPT,Amount FROM TABLE2
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 4:56 am
Hi,
If you comparing multiple column I suggest use CHECKSUM() funcation.
You can compare with CHECKSUM(Chartnumber, Dataofservices,CPT,Amount) and also you can compare your compete table or every row with CHECKSUM(*)
Take help from http://msdn.microsoft.com/en-us/library/ms189788.aspx
Ram
MSSQL DBA
November 30, 2010 at 3:11 am
Thanks for your Help but I want something different.
Table 1 has 10 columns and table two has 15 columns. I want four columns of table 1 i.e OldAr (Chartnumber, DOS,CPT and Amount) to be compared with same name columns to table 2 i.e PendingClaims . If all four columns of pendingclaims are mach with OldAr table's columns then leave them And if either of any changes occurs in four columns in PendingClaims with OldAr then display it.
I had tried with this query but not working properly as required.
Select temp.* From dbo.temp With (Nolock)
inner join dbo.Temp1 With (Nolock)
On temp.ChartNumber=Temp1.ChartNumber
and temp.DOS=Temp1.DOS
and temp.CPT=Temp1.CPT
and temp.Amount =Temp1.Amount
Where temp.ChartNumber + temp.DOS + temp.CPT + temp.Amount = Temp1.ChartNumber + Temp1.DOS + Temp1.CPT + Temp1.Amount
Union
Select Temp1.* From Temp1 With (Nolock)
Left outer join temp With (Nolock)
On temp.ChartNumber =Temp1.ChartNumber
And temp.DOS=Temp1.DOS
And temp.CPT=Temp1.CPT
And temp.Amount=Temp1.Amount
Where temp.ChartNumber is null
Can any one help it's emergency friend.
November 30, 2010 at 4:56 am
Reo (11/18/2010)
Hi,You can compare with CHECKSUM(Chartnumber, Dataofservices,CPT,Amount) and also you can compare your compete table or every row with CHECKSUM(*)
I would not use this method you will get false/positives with it over very data sets, the CHECKSUM() key will be to short.
Use HashBytes ( '<algorithm>', { @input | 'input' } )
This should work
HASHBYTES('SHA1', Chartnumber + '|' + Dataofservices + '|' + CPT,Amount)
Use should put in bars to separate the columns here is a example why.
HashBytes ('SHA1', '4040' + '') would be the same as HashBytes ('SHA1', '40' + '40')
November 30, 2010 at 5:18 am
SHumphrey_66 (11/30/2010)
Reo (11/18/2010)
Hi,You can compare with CHECKSUM(Chartnumber, Dataofservices,CPT,Amount) and also you can compare your compete table or every row with CHECKSUM(*)
I would not use this method you will get false/positives with it over very data sets, the CHECKSUM() key will be to short.
Use HashBytes ( '<algorithm>', { @input | 'input' } )
This should work
HASHBYTES('SHA1', Chartnumber + '|' + Dataofservices + '|' + CPT,Amount)
Use should put in bars to separate the columns here is a example why.
HashBytes ('SHA1', '4040' + '') would be the same as HashBytes ('SHA1', '40' + '40')
http://msdn.microsoft.com/en-us/library/ms174415.aspx%5B/quote%5D
But sir, I am not so expert to use all this, will you please help me.
Just compare four columns of table PendingClaims to OldAr table. if any change in columns value of pendingClaims with OldAr table's columns, It should pick up the value from PendingClaims table.
November 30, 2010 at 5:39 am
Compare two table:-
There are numbers of columns in table 1 and table 2, but i want to compare four columns of this two table (i.e Chartnumber,DOS,CPT,Amount). If columns of table 1 matches with table 2 columns then leave the data. else if any changes occure either of columns in table 1 then pick up all columns data from table 2 except these four. this four columns data will be from table 1. Just like while macro we do.
And If there are all four columns new data i.e not in table 2 then pick up all data from table 2.
Any one help me please, I am stuck on this.
November 30, 2010 at 5:42 am
But sir, I am not so expert to use all this, will you please help me.
Just compare four columns of table PendingClaims to OldAr table. if any change in columns value of pendingClaims with OldAr table's columns, It should pick up the value from PendingClaims table.
With out more information about the table layout and the data in the table
you could do something like this
SELECT
DISINCT t1.*
FROM
TABLE1 AS T1,
TABLE2 AS t2
WHERE
T1.Chartnumber <> T2.Chartnumber
OR T1.DOS <> T2.DOS
OR T1.CPT <> T2.CPT
OR T1.Amount <> T2.Amount
You could then used the output to update or insert
December 15, 2010 at 5:33 am
Thanks for Help,
but this query provide me the details from both the table. I want just like macro we do in excel. compare the two table and update the changes either of the table's columns (four columns which are compared).
The requirement is like this: Compare two table 1 and 2 ,of four columns which are same name in table.
If changes occure either of four columns then pick up the data from table 1 except these four columns which are change one, these four columns data will from table 2. And if all four columns data are new then take the data from table2. And i don't want the data from table 1 which are not in table 2.
I am stuck on it pls help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply