Load a tabel using stored procedure.

  • 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

    1. loading the proc result into a tabel
    2. and with a full load (delete and insert again)

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply