Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

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'

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.

share|improve this answer
    
Hi. Thanks for this. I'll give it a shot. –  idon'twearsuits Oct 27 '11 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.

share|improve this answer
    
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. –  idon'twearsuits Oct 27 '11 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 '11 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 '11 at 2:58

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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