April 16, 2015 at 11:23 am
I have 3 tables 1.Stage 2.Master 3.History all the three table having same table structure.
eg. Columns are ID , NAME, AGE id will be the key column
My project requirement is
1) when new record comes in stage, It will insert into Master.
2) when the same record comes in stage it will insert into History by comparing stage and master
3) If the same record comes in with changes in data i.e name or age in stage, for that id master record should get updated
April 16, 2015 at 12:43 pm
i hv return logic but i m not sure about it
-- Insert duplicate
;with CTE (id,name,age) as
(
Select id,name,age from stage
Intersect
Select id,name,age from master
)
INSERT INTO History
Select id,name,age from cte
-- New record
insert into Master(id,name,age)
Select s.id,s.name,s.age
from stage s
left join master M
on M.id=s.id
where M.id is null
-- Update Existing
;with CTE (id,name,age) as
(
Select id,name,age from stage
Except
Select id,name,age from master
)
Update M
SET M.name =C.name
,M.age = C.age
FROM Master M JOIN CTE C
on M.id=c.id
April 21, 2015 at 2:07 pm
You'll need to write an INSERT trigger on the Stage table. Any time a record is inserted into a table, there is a system recordset called INSERTED that is an exact copy of the row just inserted into the Stage table.
Read up on triggers and the INSERTED recordset in Books On Line.
A trigger can be any stored proc that will be fired whenever something is inserted into a table. Write your INSERT trigger's stored proc to:
First, check the data in the Master table against what is in INSERTED.
If the data there is different, write what is in the Master record out to History, and write the data in INSERTED into the Master table.
If the data doesn't exist in Master, then just write the data in INSERTED into Master.
Good Luck
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply