September 8, 2009 at 6:15 am
I want to write a procedure that will compare 2 SIMILAR tables and show the difference in the content of the table.
create table Employee1 (id int, Fname varchar(50), Lname varchar(50))
go
Insert into Employee1 values ( 1, 'Jen' , 'Ambelang' )
Insert into Employee1 values ( 2, 'Alan' , 'Eechi' )
Insert into Employee1 values ( 3, 'Steve' , 'Borders' )
Insert into Employee1 values ( 4, 'Adam' , 'Carlos' )
GO
create table Employee2 (id int, Fname varchar(50), Lname varchar(50))
go
Insert into Employee2 values ( 1, 'James' , 'Ambelang' )
Insert into Employee2 values ( 2, 'Alan' , 'Don' )
Insert into Employee2 values ( 3, 'Sams' , 'Borders' )
Insert into Employee2 values ( 4, 'Adam' , 'Carlos' )
I am able to find the affected row by using the following query
select * from employee1 except select * from employee2
also the changes in a column for a record by using the following query
select fname from employee1 where id=1 except select fname from employee2 where id=1
My question is how to write a procedure that will check all the columns in a table for a given ID.
Example
CREATE PROCEDURE COMP(@ID)
AS
FOR EACH @COLUMN IN TABLE
select @COLUMN from employee1 where id=@ID except select @COLUMN from employee2 where id=@ID
IF @@ROWCOUNT <> 0 THEN
print @COLUMN + 'is changed'
END IF
LOOP
Please help!
September 8, 2009 at 7:07 am
Thanks so much for posting up sample data!! Applause!!
select E1.id,E1.Fname as Fname1, E2.Fname as Fname2, E1.Lname as Lname1, e2.Lname as Lname2
from Employee1 E1
join Employee2 E2 on E2.id = E1.id
Where E1.Fname E2.Fname
or E1.Lname E2.Lname
The WHERE clause above is the format for comparison you should use. You have to have an "OR" for every column to be compared. There is no loop or "compare all" function to test every column in one small statement. If you have dozens of columns and want to generate the the list of columns automatically, you have to fall back on dynamic SQL. The list of columns can be found in SYSCOLUMNS.
SELECT * from SYSCOLUMNS WHERE id = OBJECT_ID(N'dbo.SOMETABLE')
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2009 at 7:34 am
For very large tables (containing the hashbytes or checksum result as part of an indexed computed column) something based around the following can perform quite well...
select e1.id,
case
when e1.Fname e2.Fname then 'FName changed'
when e1.Lname e2.Lname then 'LName changed'
end
from Employee1 e1
join Employee2 e2
on e1.id = e2.id
where hashbytes('sha1', e1.Fname + e1.Lname) hashbytes('sha1', e2.Fname + e2.Lname)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 8, 2009 at 8:51 am
Nice technique, Paul.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2009 at 11:05 pm
Thanks for the reply.
I want to display (ONLY) all the updated columns in a record. When I use CASE in SELECT it will show only the Fname or Lname. In case if both are updated (Fname & Lname) in a record how to show that.
Also in my live environment I have nearly 10 table with total 260 columns. So whenever a user updates a field in a table. I have to alert other users that this column is changed in that particular record. So writting all the 260 column in that CASE statement is a headache.
So could you suggest any dynamic way to compare all the columns for a particular ID.
September 10, 2009 at 12:30 am
Is it possible to compare in SQL or shall i use front end program to compare these?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply