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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 very very urgent

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 address

001 abc1 12122010 addr1
002 abc2 12122011 addr2
003 abc2 12122012 addr3


STATUSTable: first time

id name dob address status

001 abc1 12122010 addr1 new
002 abc2 12122011 addr2 new
003 abc2 12122012 addr3 new

Second Time: DMLTable Having more data like insert updated by The application.
id name dob address

004 abc1 12122010 addr1
005 abc2 12122011 addr2
006 abc2 12122012 addr3
001 abc1 12122010 addr1
002 abc2 12122011 addr10
003 abc2 12122012 addr11

Based on DMLTable data we need update STATUSTable like below


STATUSTable:

id name dob address status

001 abc1 12122010 addr1 Nochange
002 abc2 12122011 addr10 Updated
003 abc2 12122012 addr3 Updated
004 abc1 12122010 addr1 new
005 abc2 12122011 addr2 new
006 abc2 12122012 addr3 new

if 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 all
SELECT '002', 'abc2', '20111212', 'addr2' union all
SELECT '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 all
SELECT '002', 'abc2', '20111212', 'addr11' union all
SELECT '003', 'abc2', '20121201', 'addr13' union all
SELECT '004', 'abc1', '20101201', 'addr1' union all
SELECT '005', 'abc2', '20101201', 'addr2' union all
SELECT '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 Status
FROM @DMLTable d
RIGHT JOIN @STATUSTable s on d.id = s.id[/code]

--
Chandu
Go to Top of Page

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
Go to Top of Page

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 Status
FROM {You DMLTable here } AS d
RIGHT JOIN {Your STATUS table here} AS s on d.id = s.id[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

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 names
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 Status
FROM DMLTable d
RIGHT JOIN STATUSTable s on d.id = s.id

--
Chandu
Go to Top of Page
   

- Advertisement -