HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
file2008commandssqltextseveralagainstserverdatarun

Problem

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.

Solution

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.

Code Snippets

CREATE TABLE #Keys (YourKey int)

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

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

Context

StackExchange Database Administrators Q#7289, answer score: 6

Revisions (0)

No revisions yet.