Run several commands against data in text file (SQL Server 2008 R2)

Question

I'm new to this website so forgive me if I'm asking a rudimentary question and/or one that's been asked 100 times before. I wasn't able to find an answer during my searches here.

I'd like to have my script read in values from a txt file (one value per line) and have three different statements executed for each value.

Pseudo code:

$file = getfile('c:\file.txt');

foreach($value in $file)
{
    DELETE x WHERE x = '$value';
    DELETE y WHERE y = '$value';
    DELETE z WHERE z = '$value';
}

I understand that this may not be as easy as my pseudo code desires but all my searches on Google returned long articles with copious amounts of SQL that were way over my head.

I'm looking for examples that I can implement, good articles on the subject, and/or a slap in the face telling me to wake up and realize this is simply not easy in SQL.

Answer 1

Building on Rob's answer here's some sample code to do the work.

CREATE TABLE #Keys (YourKey int)

BULK INSERT #Keys FROM 'C:\YourFile.txt'

DELETE FROM YourTable
WHERE Id in (select YourKey from #Keys)

This will need to be cleaned up with your table and column names, but it'll do the delete as a single operation.

Answer 2

What you're suggesting here isn't particularly set-based. You might prefer to import the contents of a file into a table, and then run your statements based on joins.

So - use a BULK INSERT command to pull it in, and then don't loop. The system might end up looping for you if it figures that it'll be the most effective, but let the system make that choice for you.

Answer 3

You could use openrowset to read data from your text file. Prepare a text file called file.txt:

ID
10
20
30
40
50

A format file called values.fmt:

10.0
1
1       SQLINT   0       2       ""   1     ID                 ""

(I think the file is not working properly as it is, but you get the idea..)

And then run:

SELECT x.*
into #tempTable
FROM OPENROWSET(BULK 'c:\Users\Marian\Desktop\file.txt'
   , FORMATFILE = 'c:\Users\Marian\Desktop\values.fmt'
) as x

That will insert your text file data into a temporary table that you'll be able to use for deleting data:

delete from YourTable yt
join #tempTable tt on tt.ID = yt.ID

BTW: this is a set based operation (do it all at once, not one by one).

PS: see that you put the correct path for the files inside the first statement.