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.
Author |
Topic |
chbala85
Starting Member
49 Posts |
Posted - 2013-08-14 : 00:42:38
|
Hi all,I have two tables DMLTable and STATUSTable .DML table daily having insert.DMLTable: id name dob address001 abc1 12122010 addr1002 abc2 12122011 addr2003 abc2 12122012 addr3STATUSTable: first timeid name dob address status001 abc1 12122010 addr1 new002 abc2 12122011 addr2 new003 abc2 12122012 addr3 newSecond Time: DMLTable Having more data like insert updated by The application. id name dob address004 abc1 12122010 addr1005 abc2 12122011 addr2006 abc2 12122012 addr3001 abc1 12122010 addr1002 abc2 12122011 addr10003 abc2 12122012 addr11Based on DMLTable data we need update STATUSTable like belowSTATUSTable:id name dob address status001 abc1 12122010 addr1 Nochange002 abc2 12122011 addr10 Updated003 abc2 12122012 addr3 Updated004 abc1 12122010 addr1 new005 abc2 12122011 addr2 new006 abc2 12122012 addr3 newif id already exit compare the name ,dob,address and change status ,updated data also like above table.query or stored procedure any one . Please help me very very urgent..Thanks,Krish |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-14 : 01:37:35
|
[code]DECLARE @DMLTable TABLE(id CHAR(3), name VARCHAR(5), dob DATe, address VARCHAR(10))insert into @DMLTable SELECT '001', 'abc1', '20101201', 'addr1' union allSELECT '002', 'abc2', '20111212', 'addr2' union allSELECT '003', 'abc2', '20121201', 'addr3'DECLARE @STATUSTable TABLE(id CHAR(3), name VARCHAR(5), dob DATe, address VARCHAR(10), status VARCHAR(40))insert into @STATUSTable (id, name , dob , address)SELECT '001', 'abc1', '20101201', 'addr1' union allSELECT '002', 'abc2', '20111212', 'addr11' union allSELECT '003', 'abc2', '20121201', 'addr13' union allSELECT '004', 'abc1', '20101201', 'addr1' union allSELECT '005', 'abc2', '20101201', 'addr2' union allSELECT '006', 'abc2', '20101201', 'addr3'SELECT s.* , CASE WHEN d.ID IS NULL THEN 'New' WHEN s.ID = d.Id AND s.name = d.name AND s.Address = d.Address AND d.dob = s.dob THEN 'No Change' ELSE 'Updated' END As StatusFROM @DMLTable dRIGHT JOIN @STATUSTable s on d.id = s.id[/code]--Chandu |
|
|
chbala85
Starting Member
49 Posts |
Posted - 2013-08-14 : 01:39:49
|
Hi chandu ,In my table having 87k records how i will do.Thanks,Krish |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-14 : 02:17:03
|
[code]SELECT s.* , CASE WHEN d.ID IS NULL THEN 'New' WHEN s.ID = d.Id AND s.name = d.name AND s.Address = d.Address AND d.dob = s.dob THEN 'No Change' ELSE 'Updated' END As StatusFROM {You DMLTable here } AS dRIGHT JOIN {Your STATUS table here} AS s on d.id = s.id[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-14 : 02:50:44
|
quote: Originally posted by chbala85 Hi chandu ,In my table having 87k records how i will do.Thanks,Krish
Just Blue marked names should be your table namesSELECT s.* , CASE WHEN d.ID IS NULL THEN 'New' WHEN s.ID = d.Id AND s.name = d.name AND s.Address = d.Address AND d.dob = s.dob THEN 'No Change' ELSE 'Updated' END As StatusFROM DMLTable dRIGHT JOIN STATUSTable s on d.id = s.id --Chandu |
|
|
|
|
|
|
|