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.

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

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

    ReplyDelete
  7. Your articles and contents are encouraging.
    4liferesearchreview.com

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

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

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

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

    ReplyDelete
  12. 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
  13. Plumbing & HVAC Services San Diego
    Air Star Heating guarantees reliability and quality for all equipment and services
    Air Star Heating is specializing in providing top-quality heating, ventilating, air conditioning, and plumbing services to our customers and clients.
    Our company is leading the market right now. By using our seamless and huge array of services. Our customers can now have the privilege of taking benefit from our services very easily and swiftly. To cope up with the desires and needs of our clients we have built an excellent reputation. We are already having a huge list of satisfied customers that seem to be very pleased with our services.

    Plumbing & HVAC Services in San Diego. Call now (858) 900-9977 ✓Licensed & Insured ✓Certified Experts ✓Same Day Appointment ✓Original Parts Only ✓Warranty On Every Job.
    Visit:- https://airstarheating.com

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

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

    ReplyDelete
  16. 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
  17. This blog is very informative the stuff you provide I really enjoyed reading.
    app agency

    ReplyDelete