How to write a Stored Procedure to Select, Delete and then Insert?

Question

I am loading a sample data for my UI testing and for that I am trying to check if the data exists. If it is then delete it and insert the new one instead. I got almost 9 inserts and I am not sure if I need to check for each row if it exists then delete each row then insert.

This is the sample data that I am trying to load using SP.

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP', getDate(), getDate(), 1); 

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP1', getDate(), getDate(), 1); 

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP2', getDate(), getDate(), 2);  

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP3', getDate(), getDate(), 3); 

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP4', getDate(), getDate(), 4); 

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP5', getDate(), getDate(), 5); ; 

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP6', getDate(), getDate(), 6);  

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP7', getDate(), getDate(), 7);  

INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE) VALUES 
(212019, 'SOURCE', 'COMP8', getDate(), getDate(), 8);

Stored Procedure

CREATE PROCEDURE TRANSACTION
@TRANSACTION_ID int,
@MDATA_ATTRB varchar,
@MDATA_VALUE varchar,
@ISACTIVE bit

AS
BEGIN
IF EXISTS (SELECT * FROM TRANS_MDATA WHERE TRANSACTION_ID = @TRANSACTION_ID)

BEGIN
DELETE FROM TRANS_MDATA WHERE TRANSACTION_ID = @TRANSACTION_ID
END

ELSE
INSERT INTO TRANS_MDATA (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE)
VALUES (@TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, getdate(), getDate(), @ISACTIVE)
END 
END

EXEC [TRANSACTION]
@TRANSACTION_ID = 123456,
@MDATA_ATTRB = 'Source',
@MDATA_VALUE = 'Backend',
@ISACTIVE bit = 1

Just do an update instead of a delete/insert. Also, don't name your proc transaction, since it's a reserved keyword. — Xedni, Sep 11 '17 at 21:15
this may be the same as https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005 — Ferdinand Gaspar, Sep 11 '17 at 21:15
@Xedni. I do need to delete. if the data set exists delete else insert new record. — Mike, Sep 11 '17 at 21:26
you are not inserting data if it already exist. It is only deleting that record. Maybe you can just remove IF ...ELSE statement and just run DELETE and INSERT statements. Delete statement already has a where clause which is basically checking if transaction id exists or not — Kashif Qureshi, Sep 11 '17 at 21:28
Why do you need to delete the row first and then insert it again? That makes no sense. To be honest you could do this whole thing with MERGE. https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql — Sean Lange, Sep 11 '17 at 21:37
And based on your last question on this exact topic you should scrap this approach of doing this row by row, you should do this for your entire set in step. — Sean Lange, Sep 11 '17 at 21:39
@Sean I am checking if the data exists then delete it and then insert a new one. — Mike, Sep 11 '17 at 22:06
Checking for the existence of data, then deleting it, then inserting a new row with new values is kind of silly. This is three steps where a single update statement would accomplish the same thing. — Sean Lange, Sep 12 '17 at 13:25

score 0 · Answer 1 · answered Sep 11 '17 at 22:36

You can use EXCEPT in your insert to check for data matching across many columns:

--  Set up a CTE pseudo-table of your test data:
; WITH TestValues
AS (
    SELECT DISTINCT TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE
    FROM (
    VALUES (212019, 'SOURCE', 'COMP', getDate(), getDate(), 1)
        , (212019, 'SOURCE', 'COMP1', getDate(), getDate(), 1)
        ,(212019, 'SOURCE', 'COMP2', getDate(), getDate(), 2)
        ,(212019, 'SOURCE', 'COMP3', getDate(), getDate(), 3)
        ,(212019, 'SOURCE', 'COMP4', getDate(), getDate(), 4)
        ,(212019, 'SOURCE', 'COMP5', getDate(), getDate(), 5)
        ,(212019, 'SOURCE', 'COMP6', getDate(), getDate(), 6)
        ,(212019, 'SOURCE', 'COMP7', getDate(), getDate(), 7)
        ,(212019, 'SOURCE', 'COMP8', getDate(), GETDATE(), 8) 
    ) t1 (TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE)
    )
    --  Insert anything not already in the target table (EXCEPT):
    INSERT INTO TRANS_MDATA(TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE)
    SELECT TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE
    FROM TestValues
    EXCEPT
    SELECT TRANSACTION_ID, MDATA_ATTRB, MDATA_VALUE, CREATED_TIME, LAST_MOD_TIME, ISACTIVE
    FROM TRANS_MDATA

How to write a Stored Procedure to Select, Delete and then Insert?

1 Answers1