Please navigate to the bottom of the page for Table of Contents

Wednesday, March 20, 2013

SQL read text from a file

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. 

So 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?

Create 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
   FROM 'c:\temp\customerids.txt'
         ROWTERMINATOR ='\n'

Now your select statement becomes quite simple: 
select * from Customer c
inner join DisabledCustomers dc on c.Id = dc.Id

Many production systems have BULK INSERT disabled? As an exercise to you, my dear reader, I look forward to seeing some innovative solutions to that.


  1. done lot of bulk insert while converting unix system to


  2. The blog or and best that is extremely useful to keep I can share the ideas
    of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    tanki online | 2048 game| tanki online game

  3. Approve, we should take a gander at Mark Twain's Huckleberry Finn to perceive how this functions. The novel opens with the acclaimed words "You don't think about me, without you have perused a book by the name of 'The Adventures of Tom Sawyer,' however that ain't regardless." So who or what is the great reader, the Stradivarius, to be here?text to speech online free save to mp3

  4. This is sure to help with enhancing proficiency aptitudes and having better broad speech.

  5. In terms of competition levels. challenge we certainly have when possessing an exercise is so it will be bad weather as well as get these consumers in the front door. We have been generally researching ways to equally develop the organization and offer the general public.

  6. Each time I used to always check blog posts within the first hours in the break of day, because I like to get information increasingly more.

  7. Your articles and contents are encouraging.

  8. That they had well project management skills and were in communication frequently website design agencies was receptive to feedback and implemented it accordingly. The collaboration was a hit.

  9. Keep the ball rolling you have done the great job here.
    Web Site

  10. The superb highly informative blog I’m about to share this with all my contacts.
    San Francisco web design

  11. You have really selected the suitable topic; this is one of my favorite blogs.
    user experience agency