Insert Rows Replacing if Already Exists

Transact-SQL
1

Hi experts,
In MySQL, you can replace/overwrite existing rows as you are inserting (if that key already exists)

Can this be done in TSQL? I need to insert the newer row if the key already exists in the destination table. Hope this makes sense.
Thanks

2

couple of ways. I like this approach myself

;with UpdateProductIssues
as
(
	select * from source
)
MERGE dbo.FactProductIssues AS TARGET
USING UpdateProductIssues AS SOURCE 
ON (rtrim(ltrim(TARGET.Product)) = rtrim(ltrim(SOURCE.product))) 
--When records are matched, update the records if there is any change
WHEN MATCHED 
THEN UPDATE 
	SET TARGET.b1_1 = SOURCE.b1,
	TARGET.b1_2 = SOURCE.b2,
	TARGET.b1_3 = SOURCE.b3,
	TARGET.b1_4 = SOURCE.b4,
	TARGET.b1_5 = SOURCE.b5
--When not matched insert
WHEN NOT MATCHED BY TARGET 
THEN INSERT (Product, [b1_1], [b1_2], [b1_3], [b1_4], [b1_5]) 
	VALUES (SOURCE.Product, SOURCE.[b1], SOURCE.[b2], SOURCE.[b3], SOURCE.[b4], SOURCE.[b5])
--When there is a row that exists in target and same record does not exist in source then delete this record target
WHEN NOT MATCHED BY SOURCE 
THEN DELETE;
3

The normal pattern for this type of operation is called an UPSERT. That is basically a transaction that performs an UPDATE and then an INSERT as 2 separate operations within the same transaction.

BEGIN TRANSACTION;

UPDATE t
Set t.col1 = x.col1
FROM table1 t
JOIN table2 x ON x.key = t.key
WHERE {if needed};

INSERT INTO t
SELECT x.col1
FROM table2 x
JOIN table1 t ON t.key = x.key
WHERE {if needed};

COMMIT TRANSACTION;

The form of the UPDATE or INSERT will be based on the source data. It can be much simpler if this is a procedure that accepts parameters for each column (for example).

The other option - if you want to do this in a 'single' operation would be a MERGE. Look in BOL for the full syntax.

1 Like
4

jeffw8713, I'm using your technique for an UPSERT. I'll reply back. Thanks

5

I ran this:
BEGIN TRANSACTION;
INSERT INTO t
SELECT x.[QuoteID],
x.[DeptName
FROM MyDb.dbo.tblQuote x
JOIN MyDb.dbo.tblQuoteTest t ON t.QuoteID = x.QuoteID
WHERE x.BrId = 1012 and (x.quoStatus IN('Active', 'Hold'));
COMMIT TRANSACTION;

I get error "Invalid object t" on the INSERT line. And do I need to list both columns in the INSERT same as I did for the Select? Thanks

6

Sorry - you have to specify the table in the insert statement.

7

Jeff, I made that correction and it checks out syntactically now. I ran the entire block and it detected an duplicate PK (which is the QuoteID column). As you remember, my intent is to update existing rows with data from the source table... and insert new rows based on the Where clause. Any ideas? Thanks

Violation of PRIMARY KEY constraint 'PK_tblQuote'. Cannot insert duplicate key in object 'dbo.tblQuote'. The duplicate key value is (1573-11).

8

Correct - you have to put in a condition to check for existence, or rather a NOT EXISTS. Just to confirm - you want to insert into tblQuoteTest rows from tblQuote that do not already exist in tblQuoteTest?

Select *
  From dbo.tblQuote       q
 Where q.BrId = 1012 
   And q.quoStatus In ('Active', 'Hold')
   And Not Exists (Select * From dbo.tblQuoteTest qt Where qt.QuoteID = q.QuoteID)

If this query returns the rows you want to insert - then you can change it to an insert statement. You need to insert all required columns.

You need to perform the update first - and then perform the insert. If you do the insert first - then the update will update the rows you just inserted as well as the rows that were not updated.

You can use a JOIN or EXISTS for the update or the insert. For an insert, if you want to use a join then you can use an outer join and only include rows where the joined tables key column is null.

Either way - the first step should always be writing the query to get the rows and validating the logic is working before changing it to an insert or update.

1 Like
9

You sir, have the patience of Job. Thanks very much for your detailed explanation. I'm not there yet, but things are progressing.