Comparing Data between two identical tables

[captainpicart]

Hi Techies,

I have two identical tables (1) Employee (2) PreviousEmployee. Now, I need to create only one query/storedProcedure which has to do the following:

1. Get only records which exist only in Employee table but they do not exist in PreviousEmployee table. These records should contain a new column called "Status" and Its value should be "Added".

2. After the above result set, only get modified rows between two tables. For example: employeeID 0067 exist in both tables. and it has been modified in either of these two tables. Still get the modified row from Employee table. THe value of "Status" column for each row would be "Updated".

Thanks!

 

[markros]

Check

http://www.mssqltips.com/tip.asp?tip=1023

You can do

select AllFields,  cast('Added' as varchar(10)) as Status
into EmployeeChanges
 from Employee E where not exists (select 1 from
PreviousEmployee P where P.EmployeeID = E.EmployeeID)

insert into EmployeeChanges
select *, 'Updated' as Status from Employee E
INNER JOIN PreviousEmployee P on E.EmployeeID = P.EmployeeID
where checksum(E.Col1, E.Col2, ...) <> checksum(P.Col1, P.Col2,..)

PluralSight Learning Library

 

[captainpicart]

This is Great! Thanks Markros!

Can you recommend really good books which teach solid T-SQL programming?

Thanks.

 

[markros]

Itzik Ben-Gan books are great, although I haven't read them myself personally. I recommend to get any of his books.

PluralSight Learning Library