0

I have an XML data source stored in a Temporary Table using XQuery, and have successfully inserted this into the database.

My problem is that I don't know how to check if something exists first, and then delete it, base on the statement. Here's the insertion code:

SELECT
    N.value('id-number[1]', 'VARCHAR(10)') as IdNumber,
    N.value('irrelevant1[1]', 'VARCHAR(12)') as Irrelevant1,
    N.value('irrelevant2[1]', 'VARCHAR(14)') as Irrelevant2,
    N.value('irrelevant3[1]', 'VARCHAR(16)') as Irrelevant3,
    N.value('irrelevant4[1]', 'VARCHAR(18)') as Irrelevant4,
    N.value('irrelevant5[1]', 'VARCHAR(20)') as Irrelevant5,
INTO #TempTable
FROM @xml.nodes('nodes/here') AS X(N)
INSERT INTO [main-entry] SELECT * FROM #TempTable

Now, what I want to do, before insertion, is to find out whether or not this record, based on id-number, exists in the database. If it exists, then delete it. It has a CASCADE DELETE option on the child records, so those should also be deleted along with the main entry record.

Here's what I'm using:

IF (SELECT IdNumber FROM #TempTable)  IN (SELECT [id-number] FROM [main-entry]) 
    BEGIN
        DELETE FROM [main-entry] WHERE [id-number] = ''
    END

This obviously won't work since both are selecting a large amount of id-numbers.

How do I do this?

  1. If id-number exists in both the main table, and in the #TempTable, DELETE it.
  2. If it does not exist in both, INSERT records. (insert code is finished)

I do have a unique index on id-number, so it will not allow me to insert anything else if that number already exists. So, I figured the best way to handle this is to delete said record.

I've tried a MERGE statement, but I can't add INSERT code to WHEN MATCHED THEN, so I would have to run the query twice to insert. WHEN MATCHED THEN DELETE DOES work, but it doesn't allow me to insert additional records.

  • 1
    why don't use merge with delete option and then insert as you are doing now? do you want both operations in one sql, any specific reason you wanted to do in one shot? – radar Jan 06 '15 at 18:47

1 Answers1

0

you can use MERGE to delete the entries and then use your existing code for insert.

MERGE [main-entry] as Dest
USING ( select * from #TempTable 
        ) as Source
on Dest.[id-number] = Source.[id-number]
WHEN MATCHED
   THEN DELETE
radar
  • 13,270
  • 2
  • 25
  • 33
  • Sorry, please see the updated question. I can't use `MERGE` because I need it to also `INSERT`. I've tried it, and it doesn't work. It doesn't let me use `INSERT` code, and it doesn't execute the `WHEN NOT MATCHED THEN` part after deleting. –  Jan 06 '15 at 18:44
  • Also, I believe SQL Server will not allow `Dest.id-number`, and it will instead require `Dest.[id-number]`. I could be wrong, but it doesn't allow me to use dashes outside of those brackets. –  Jan 06 '15 at 18:46
  • Shortly after you posted this, I said to myself, "But I tried that!", then I realized I didn't try closing the statement after `WHEN MATCHED THEN DELETE` by adding a semicolon to terminate it (`WHEN MATCHED THEN DELETE;`. Thanks a ton! –  Jan 06 '15 at 18:55