3

I will be inserting rows using an INSERT statement where the values are static and not already in the database. Something like:

INSERT INTO MY_TABLE (ID, NAME) VALUES(123, 'Herbert')

If MY_TABLE already has a row with ID equal to 123 but the NAME is Herbie, how can I keep the new data in the INSERT statement (123, Herbert) and discard what's already in MY_TABLE (123, Herbie)?

I know about the SQL Server IGNORE_DUP_KEY setting, but I think that ignores the data in the INSERT statement and keeps the data that's already in the table. I want the reverse.

Update

To clarify, I don't want to change the INSERT statement. I want to keep it the same. I want to do the work on the server side somehow. Basically IGNORE_DUP_KEY is what I want, except its keeping the wrong data.

Update 2

I think what I am trying to do is different than the duplicate question mentioned. I don't want to change the INSERT statement at all. I want to handle it on the backend either with a SQL Server setting that I don't know about, or perhaps by creating a trigger.

Community
  • 1
  • 1
  • 2
    Possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) – ggradnig Jul 31 '18 at 18:42
  • 1
    if the suggested duplicate isn't up your alley, you can look into [**MERGE**](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017). It's supported in Azure Databases. I understand you want to change the insert statement... but I don't see a way around this purely in SQL Server. You could check it on the application side if you want, but you didn't tag anything other than the RDMS. – S3S Jul 31 '18 at 18:49
  • Could a trigger be created that would do it? –  Jul 31 '18 at 18:53
  • @BarrettNashville I think the link from ggradnig is the best solution. Not the one marked correct but the one with the insert/merge statement and 350 votes. – Holmes IV Jul 31 '18 at 19:13
  • You could make two primary keys (ID, NAME) at the same time, this would make it possible to insert duplicates on either one of them. like (123, 'Herbert') AND (123, 'Herbei'). – iSR5 Jul 31 '18 at 19:27

2 Answers2

0

Ok, I think I've got something that works. It's just a basic INSTEAD OF INSERT trigger that deletes the row on the table if it already exists and then inserts the new row. This trigger is run every time a row is inserted into the table.

CREATE TRIGGER TR_MY_TABLE_IN ON MY_TABLE
INSTEAD OF INSERT
AS
BEGIN 
    SET NOCOUNT ON;

    DELETE FROM MY_TABLE WHERE ID IN (SELECT ID FROM INSERTED)
    INSERT INTO MY_TABLE SELECT * FROM INSERTED

END
  • That trigger is run for every INSERT_statement_ not every row. So you need to handle the case where multiple rows are INSERTed in a single statement. And you should probably use MERGE instead of DELETE/INSERT. The PK values aren't changing, so no need to delete rows. – David Browne - Microsoft Jul 31 '18 at 19:38
  • Ok, good point. I didn't think about that. I changed the `DELETE` statement to `WHERE ID IN...` instead of `WHERE ID = ...`. –  Jul 31 '18 at 20:09
0

As I see it, you have 2 choices.

First you can delete from the table where the ID = your ID coming in. This can be done without having to worry if the ID already exists since deleting a record that doesn't exist does not produce an error in SQL. If you insist on only using an Insert statement, then this is your option.

Add

Delete MY_TABLE where ID = 123
INSERT INTO MY_TABLE (ID, NAME) VALUES(123, 'Herbert')

Second, you can add a "check" to see if the ID already exists and have an Insert statement and an Update statement depending on whether the ID exists already.

If(select ID from MY_TABLE where ID = 123)
    Update MY_TABLE
     Set NAME = 'Herbert'
    Where ID = 123
ELSE
    INSERT INTO MY_TABLE (ID, NAME) VALUES(123, 'Herbert')
Randy C
  • 16
  • 3