0

I have a SQL script which is meant to work as a patch, so that it's only intended to run one time to modify some entries in a database:

INSERT INTO Table1 ...
INSERT INTO Table2 ...
etc...

If customer service (or whoever is applying the patch) were to run this script multiple times accidentally, there would be duplicate entries made into certain tables which would have an undesirable effect.

My thought was to just have a DELETE statement precede each INSERT so that if the entry which was about to be inserted already existed, it would be deleted, avoiding a duplicate. If the entry wasn't already there, the DELETE statement would have no effect on the database.

Is there a better way to handle this issue, and if so, why is the suggested approach considered better or more appropriate? Performance isn't a concern since the script is intended to only run once.

Note that I'm not asking if another way to do this exists. I am asking, given the CONTEXT (the script is a patch which is meant to be run only once, ever), is the solution described above appropriate or is there a reason why I should avoid going with my approach and solve the issue another way?

Mike
  • 825
  • 3
  • 12
  • 30

1 Answers1

1

I would use merge statement:

       merge into  [dbo].[Table] target using (
        VALUES             
        (100001, 'Value1', NULL, 1)
       ,(100002, 'Value2', NULL, 1)            
        ) 
        as source ([Id], [Column1], [Column2], [Column3])    
        on target.[Id] = source.[Id]

       when matched then update set 
        [Column1] = source.[Column1]
       ,[Column2] = source.[Column2]
       ,[Column3] = source.[Column3]

       when not matched by target then
       insert ([Id], [Column1], [Column2], [Column3]) values
        (source.[Id],source.[Column1],source.[Column2],source.[Column3])
       ;
Zen
  • 244
  • 1
  • 3
  • 15