SQL Bulk Deletions from File

Question

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?

you can delete with a join. http://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server — Jeremy, Aug 10 '16 at 15:12

score 1 · Accepted Answer · answered Aug 10 '16 at 15:12

1

Why not simply like this:

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

answered Aug 10 '16 at 15:12

HoneyBadger

14,750
3
34
48

Probably cause I was majorly overthinking the problem, my goodness. Thanks! Will accept when eligible. – Benjamin S Aug 10 '16 at 15:15

score 0 · Answer 2 · answered Aug 10 '16 at 15:13

0

;WITH CTE AS (SELECT T.* FROM TEST T WHERE T.KEY1 IN 
               (SELECT KEY FROM TestTemp))
 DELETE CTE;

or other such way of building CTE from contents of TestTemp

answered Aug 10 '16 at 15:13

Cato

3,652
9
12

SQL Bulk Deletions from File

2 Answers2