Forum Discussion

SKA0072024's avatar
SKA0072024
Copper Contributor
Feb 03, 2024

Stored Procedure Modification

Can anyone please share a sample query for the below .

 

1.To modify an existing stored procedure to select the sp execution results & insert the same in to another table.

 

2. To extract the sp results & insert the data with delta load.

2 Replies

  • rmeldrum's avatar
    rmeldrum
    Copper Contributor

    your question is a bit vague so my answer is a bit of a guess but maybe you want something like this:

     

     

    -- Modify Stored procedure 
    ALTER PROCEDURE [dbo].[uspSPName]
        @VariableName [int]
    AS
    BEGIN
        SET NOCOUNT ON;
    
        SELECT *
    	FROM [dbo].[tableName]
    END;
    
    -- Select SP results 
    INSERT INTO #tmp EXEC [dbo].[uspSPName]
    
    -- DELTA Load
     MERGE mergeTable AS tgt
        USING (SELECT * from #tmp) AS src(id, name)
            ON (tgt.id = src.id)
        WHEN MATCHED
            THEN
                -- RUN UPDATE STATEMENT 
        WHEN NOT MATCHED
            THEN
                -- RUN INSERT STATEMET

     

     

     

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    That's really to less on information.
    Please post table design as DDL, some sample data as DML statement and the expected result.

Resources