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.

3 Answers 3


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'

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.

  • Hi. Thanks for this. I'll give it a shot. Oct 27, 2011 at 16:21

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.

  • Hello. Thanks for your suggestion. What does "set-based" mean and why is it important in this context? Also, importing into a table first was one the options I had read about and since I do this operation so infrequently I was hoping for something that could save me time from my current method but not not require me to learn to much. Don't get me wrong, because I'm not complaining about your suggestion, but I think I may find out that my current method, albeit as painful as it is, is better than the others for me. Oct 27, 2011 at 1:33
  • 2
    SQL Server is optimized for set based operations. It will perform operations much faster when you throw a couple thousand rows at the problem compared to a single operation at a time.
    – mrdenny
    Oct 27, 2011 at 2:09
  • 1
    Using BULK INSERT is very straight forward. And altering the commands you already have should be easy too. But I guess it depends on whether you want to do something without learning anything. Another option could be PowerShell, which can very easily run through the lines of a file, calling a set of commands for each.
    – Rob Farley
    Oct 27, 2011 at 2:58

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


A format file called values.fmt:

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.