September 13, 2011 at 2:02 pm
Hi I got a table(TABLE A) which will have inserts and updates from different source. Now i need to design a package which will copy Data from TABLE A to TABLE B(Incremental Load)
When ever i get a new record in Table A i will insert that record in Table B and if we have an update in Table A then update that in Table B
Now i designed the package and it's working fine. But now i got an new issue where we have an insert of a record and update for that record on the same day. How do i handle that as i used conditional split which is redirecting all the new rows towards insert and old rows(but not the new rows which had an update for them) to updates.
Can someone help me out with this please.
Thank You
September 13, 2011 at 5:33 pm
What's the business need for knowing that something was inserted and updated on the same day?
(I may be making assumptions,but ) It sounds like you're building/updating your data warehouse periodically (each night). Any rows that were in the DW, but have changed in the source get updated in the DW. Any rows that are not in the DW, get inserted. Is this your situation?
Rob
September 14, 2011 at 8:18 am
Sorry for confusing you. We get a record inserted today and may have an update for that record as well today.
It's my mistake that i haven't gone through that completely as i will be getting the iinserted record with updated value.
Sorry once again for confusing you.
September 14, 2011 at 9:00 am
I'm not totally following you, but it sounds like you may be getting transactions and need to apply these changes to your data warehouse. Could you do something like:
1) Do a COUNT(*) on all transaction records from Table A by unique business key
2) For records where the count is 1 (only 1 transaction record for that id) process like you are now (insert new records and update existing records).
3) For records where the count is greater than 1, these transactions need to be processed in order. You could run these through a SQL command; the downside is that these are processed a record at a time -- but this sounds like what you need for this scenario.
HTH,
Rob
September 14, 2011 at 9:16 am
Hey,
Can you create stored procedure for that?
Create procedure usp_insert_update_move
as
begin
insert table a
select * from "Different source"
update table a
set "columnname"= ....
where =...
select * into #aa from table a
insert table B
select * from #aa
end
and use this procedure in Execure SQL Task.
That's it
Thanks
Bhavesh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply