Stored procedure INSERT from view into Table based on conditions

Question

My stored procedure calls an API which generates a consumable view of data. I need to insert the records from the generated view into local table.

So far, the procedure looks like below.

The generated consumable view is [vw_OriginalView_Temp]
Local table were data needs to be inserted is LocalTable. This table won't have any primary key.

Conditions for insert and update and skipping duplicates

SKIP - if a record with exact combination of CustomerGatcaStatusId, AccessNumber, AscertainMethodID, ExtendedStatusId, LastModifiedDate, LastModifiedBy, LastReviewedBy exists in local table.
UPDATE - if a record with exact combination of CustomerGatcaStatusId, AccessNumber, AscertainMethodID, ExtendedStatusId exists, then update the values of LastModifiedDate, LastModifiedBy, LastReviewedBy with the values from the view vw_OriginalView_Temp
INSERT - if any combination of CustomerGatcaStatusId, AccessNumber, AscertainMethodID, ExtendedStatusId doesn't already exist in Local table.

Please suggest and feel free to comment if you need further clarifications. Appreciate assistance.

ALTER PROCEDURE [dbo].[StoredProcedure1]
AS
BEGIN
    DECLARE @return_value INT,
            @RetCode INT,
            @RunID,
            @IntraDayID INT

    SET @RunID = NULL
    SET @IntraDayID = NULL

    EXEC @return_value = [Staging].[API_GenerateTempView]
                           @SchemaName = N'XXXX',
                           @ViewName = N'vw_OriginalView',
                           @ColumnList = N'CustomerGatcaStatusId, AccessNumber, AscertainMethodID, ExtendedStatusId, LastModifiedDate, LastModifiedBy, LastReviewedBy',
                           @OrderByList = NULL,
                           @ResultSet = 1,                           
                           @RunID = @RunID,
                           @IntraDayID = @IntraDayID,
                           @RetCode = @RetCode OUTPUT


    INSERT INTO LocalTable(CustomerGatcaStatusId, AccessNumber, AscertainMethodId, ExtendedStatusId, LastModifiedDate, StatusCode, LastModifiedBy, LastReviewedBy)
       SELECT 
           CustomerGatcaStatusId, AccessNumber, AscertainMethodId, 
           ExtendedStatusId, LastModifiedDate, '11', LastModifiedBy, LastReviewedBy 
       FROM 
           [XXXX].[vw_OriginalView_Temp]

    --IF EXISTS (SELECT * FROM AeoiSdtTemp)
    --BEGIN
    --  DELETE FROM AeoiSdtTemp
    --END
END

You might find the MERGE statement useful: https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx — Paul Michaels, Apr 10 '18 at 06:32
@pm_2 exactly, i believe merge statement will be useful, just trying to get my head around it. — Maverick, Apr 10 '18 at 20:39

score 0 · Answer 1 · answered Apr 10 '18 at 01:31

What about something like for the insert

INSERT INTO LocalTable(...)
SELECT (...) FROM [XXXX].[vw_OriginalView_Temp] _Temp left 
outer join LocalTable _Local on _Temp.a = _Local.a and _Temp.b = _Local.b 
where _Local.a is null and _Local.b is null

and for the update

UPDATE _Local
SET _Local.c = _Temp.c, _Local.d = _Temp.d
FROM LocalTable _Local 
left outer join TempTable _Temp on _Temp.a = _Local.a and _Temp.b = _Local.b and _Temp.c = _Local.c and _Temp.d = _Local.d
where _Temp.a is not null and _Temp.b is not null and _Temp.c IS NULL and _Temp.d IS NULL

untested but I guess you get the idea?

For the insert, doing a left outer join and testing for NULL checks that there is no existing record matching this condition For the update we use the same mechanism but sometimes ensure NULL, sometimes NOT NULL to make sure we have a record partially matching

Stored procedure INSERT from view into Table based on conditions

1 Answers1