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'
   WITH
      (
         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.

4 comments:

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

    ReplyDelete

  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

    ReplyDelete
  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

    ReplyDelete