Hello!
I have a already created stored procedure named Membership which now is used to provide input for another application. Now the application is being replaced and I need to store the output of the procedure in a tabel in our database. The procedure gives me aggregated Member data (like in Year 2020, in the Month of April, for Age 30, there are 35 Members). So the output of the procedure is Year, Month, Age, No. of Members. It will be a full load everytime. So question is: How do I include the logic of
into the exisitng Membership procedure?
You want your proc to have two options? Like 'Incremental' and 'Full', is that right?
Full is easy, just add:
TRUNCATE TABLE ...
INSERT TABLE ... results of proc
to your proc.
Incremental is harder, but not that much, assuming you have a robust primary key in place. Use MERGE (or separate INSERT/UPDATE/DELETE statements, if preferred).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 7, 2021 at 11:36 am
There may be a million details on this, but I'd start with two general concepts.
For emptying the table in order to reload it, TRUNCATE is your best bet. There are dependencies & gotchas around using it of course. However, it'll get the job done in most instances.
As for loading the table, I'd modify your procedure so that you use INSERT...SELECT syntax. That's the easiest way to get it done. That way, the existing query and the work you've done building are not in any way tossed aside.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2021 at 7:59 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply