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.
Nikhil Singhal's blog dedicated to helping you master the art of programming interviews.
- Introduction
-
Beginners
Simple coding questions–Part 1 How to find if a number is a Palindrome? Linked lists demystified Recursion–concepts and code Linked Lists – Sorting, Searching, Finding Maximum and Minimum Reverse a Linked List Searching algorithms–Linear search Binary Search String manipulation can be fun String pattern matching String functions galore Pass by value versus reference in C# How to find if a number is perfect square
-
Advanced
Lost in a Forest of Trees The Ins and Outs of a Binary Search Tree Simple Patterns: Singleton Pattern Simple Patterns: Repository Pattern Simple Patterns: Factory Pattern Implement a basic Stack using linked List Implement a Queue data structure using a linked list Operator overloading and pairing rules in C# LINQ Query, Selection, Partial Selections and Aggregations Explain System.IO and System.IO.Compression namespaces with an example How to Boost your Self-Confidence Distributed vs Parallel computing SOA interview questions Data migration strategies and design patterns
-
ASP.NET
How to redirect user to another page using ASP.NET Return different HTTP response codes from Response... Tracing page execution in ASP.NET ASP.NET page validation controls ASP.NET 4 site navigation using sitemaps How do you serialize an object to and from XML Manage state across entire application in ASP.NET How to monitor file system changes using FileSystemWatcher in C# ASP.NET @ Page directive How to add HTML Server Controls to a Web Page Using ASP.NET ASP.NET AJAX using UpdatePanel control ASP.NET Session modes explained Explain ASP.NET data binding using DataSets and DataSourceControls ASP.NET HttpModule explained ASP.NET HttpHandlers Asp.Net MVC interview questions
-
jQuery
jQuery AJAX functions part 1–the load() method jQuery AJAX functions part 2–get(), post(), getScript() and getJSON() jQuery AJAX functions part 3–ajax() Differentiate between alert(), prompt() and confirm() methods jQuery fadeIn, fadeOut and fadeTo effects jQuery Selectors reviewed jQuery to block paste in a textbox jQuery to create default text for a textbox jQuery to select/deselect all items in a CheckBoxList jQuery to show big image on hover
- SQL
- LINQ
- JAVA
-
Entity Framework
Entity Framework interview questions Entity Framework and eager loading of related entities interview questions Entity Framework and lazy loading interview questions Entity Framework transaction scope examples Entity Framework – what are the different ways to configure database name? Entity Framework - Explain ENUM usage in EF5
Please navigate to the bottom of the page for Table of Contents
Wednesday, March 20, 2013
Subscribe to:
Post Comments (Atom)
done lot of bulk insert while converting unix system to asp.net..
ReplyDeleteGreat Article
DeleteIEEE Final Year Projects for CSE
IEEE Project Centers in Chennai
Great Article
ReplyDeleteC# Training
C# Online Training
C-Sharp Training
Dot Net Training in Chennai
.Net Online Training
ASP.NET Training
ReplyDeleteThe 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
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
ReplyDeleteexploration lite blocklauncher instafollow
ReplyDelete