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.

23 comments:

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

    ReplyDelete
  2. 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
  3. This is sure to help with enhancing proficiency aptitudes and having better broad speech. notevibes.com

    ReplyDelete
  4. In terms of competition levels. https://onlineearningmentor.com/search-engine-optimization 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.

    ReplyDelete
  5. 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.
    http://listleveragereview.net/

    ReplyDelete
  6. 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.

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

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

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

    ReplyDelete
  10. Compre documentos en línea, documentos originales y registrados.
    Acerca de Permisodeespana, algunos dicen que somos los solucionadores de problemas, mientras que otros se refieren a nosotros como vendedores de soluciones. Contamos con cientos de clientes satisfechos a nivel mundial. Hacemos documentos falsos autorizados y aprobados como Permiso de Residencia Español, DNI, Pasaporte Español y Licencia de Conducir Española. Somos los fabricantes y proveedores de primer nivel de estos documentos, reconocidos a nivel mundial.

    Comprar permiso de residencia,
    permiso de residenciareal y falso en línea,
    Compre licencia de conducir en línea,
    Compre una licencia de conducir española falsa en línea,
    Comprar tarjeta de identificación,
    Licencia de conducir real y falsa,
    Compre pasaporte real en línea,

    Visit Here fpr more information. :- https://permisodeespana.com/licencia-de-conducir-espanola/
    Address: 56 Guild Street, London, EC4A 3WU (UK)
    Email: contact@permisodeespana.com
    WhatsApp: +443455280186

    ReplyDelete
  11. really infomative and eduvcative article thanks publisher for sharing this info with us massage gaming chair with footrest whatsaup flippzilla

    ReplyDelete
  12. informative article thanks for shairng this wonderful info gossip mouth

    ReplyDelete
  13. I'm certainly very happy to read this blog site posts which carries plenty of helpful data, thanks for providing such information.
    design beast review

    ReplyDelete
  14. This blog is very informative the stuff you provide I really enjoyed reading.
    app agency

    ReplyDelete
  15. Nice answers in replace of the question with real point of view and explaining about that.
    top digital design agencies

    ReplyDelete
  16. I'm certainly very happy to read this blog site posts which carries plenty of helpful data, thanks for providing such information.
    UI UX service

    ReplyDelete
  17. I'm also visiting this site regularly, this web site is really nice and the users are genuinely sharing good thoughts.
    UX UI design studio

    ReplyDelete
  18. Understanding what is expected out of an assignment is the first step of assignment writing. Reading the assignment topic thoroughly, analyzing it and any instructions given with the assignment help you get a clear picture of what you need to include in your assignment writing. prepare assignment

    ReplyDelete