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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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