SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 very very urgent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chbala85
Starting Member

49 Posts

Posted - 08/14/2013 :  00:42:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 08/14/2013 :  01:37:35  Show Profile  Reply with Quote
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


--
Chandu
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 08/14/2013 :  01:39:49  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 08/14/2013 :  02:17:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 08/14/2013 :  02:50:44  Show Profile  Reply with Quote
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

Edited by - bandi on 08/14/2013 02:51:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000