Benjamin S Benjamin S - 3 months ago 7
SQL Question

SQL Bulk Deletions from File

I've been trying to figure out the best way to delete a number of rows from a table based on the contents of a file (CSV in my case), though I'm having difficulty with what may be best practices. The CSV i'm creating will contain keys to be deleted out of the main table.

I've come up with the idea of inserting those keys into a temp table, then running a cursor over that data set to delete each record out of the main table. I'm just worried this is a much slower operation than some other way of doing things that i'm missing out on. My cursor is below:

DECLARE @keyTemp char(10)

DECLARE cursorName CURSOR
LOCAL SCROLL STATIC
FOR
SELECT * FROM TestTemp
OPEN cursorName
FETCH NEXT FROM cursorName
INTO @keyTemp
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM Test WHERE Key1 = @keyTemp
FETCH NEXT FROM cursorName
INTO @keyTemp
END
CLOSE cursorName
DEALLOCATE cursorName


Is there a better way to do this?

Answer

Why not simply like this:

DELETE FROM Test
WHERE EXISTS (
                 SELECT    NULL
                 FROM      TestTemp TT
                 WHERE     TT.KeyTemp = Test.Key1
             )
Comments