CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2014sql-insertsql-delete
user2885188
user2885188

Reputation: 11

If row exists then delete else Insert into a table using SQL Server stored procedures in SQL Server 2014

I have two tables Table1 and Table2.

Now I want to write a stored procedure where if the record exists it will delete that record and if the record doesn't exists then it will insert the record.

NOTE: I don't have any primary key in the table. I may need to make a key based on multiple columns.

USE lan_db;  
GO  

CREATE PROCEDURE dbo.udm_to_app_invoice   
AS   
    SET NOCOUNT ON;  

    INSERT INTO dbo.dummy3
        SELECT 
            poc_name,
            poc_code,
            salesrep_code,
            invoice_date,
            product_name
        FROM
            [dbo].[src_nota_fiscal]
        WHERE
            LAST_UPDT = (SELECT MAX(last_updt) FROM DBO.SRC_NOTA_FISCAL); 
GO

Upvotes: 1

Views: 5673

Answers (1)

TT.
TT.

Reputation: 16146

Look into T-SQL IF...ELSE, that will probably be easiest.

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

The boolean expression can be a subquery where you can use the EXISTS operator.

Specifies a subquery to test for the existence of rows.

To your question, the answer in pseudo SQL:

IF EXISTS(<SELECT statement that selects the record>) 
    <DELETE statement> 
ELSE 
    <INSERT statement>

An alternative would be to use the MERGE statement. However it is a cumbersome statement and IMO not that intuitive.

Upvotes: 1

Related Questions