Consider the following scenario: Your database has a table called Customer (who doesn't these days) with millions of rows (lucky you). You production support team just called you in the middle of the night with a live site issue - Around 10,000 customers are getting a message that their logins have been disabled. And they send you a text file with 10,000 customer id's :-)
Great! Now you need to look these customers up in the database and enable their access again.
how can you load these 10K values from a text file into SQL? One way
would be to edit the text file, replace each new line with a comma and
then use the resulting string in the select clause as shown:
select * from Customer where Id in (1, 2, 6, 8, 10)
Now this might prove to be too much non-techy way. You are a programmer, right? You don't hard-code stuff! You write code!!
So how can you load these disabled customer list in the database programatically?
a temporary table and call it DisabledCustomers with just one column
(name it Id and make sure it allows nulls and is not primary key - just
in case your list from support has duplicates).
Run the following SQL to load the values from the file into this table.
BULK INSERT dbo.DisabledCustomers
Now your select statement becomes quite simple:
select * from Customer c
inner join DisabledCustomers dc on c.Id = dc.Id
production systems have BULK INSERT disabled? As an exercise to you, my
dear reader, I look forward to seeing some innovative solutions to