SQL with Manoj

Importing records from an Excel is a very simple task. Load your data in Excel with appropriate headers and Run the Import/Export Wizard, your records are transfered from Excel sheet to a MS SQL table.

But what if a single Sheet contains multiple record sets with variable headers. Like First 100 rows of Customer data with 10 headers. Then just below 50 rows of Order data with less than 10 or more than 10 headers.

Seems a bit difficult but not impossible. Its tricky though, lets see how:

Lets us suppose your Excel file is in following format shown in image below (Fig-1):
1. Contact recordset &
2. Sales Order recordset

Fig-1 Sheet with multiple recordsets

Now select the Contact recordset including headers as shown in Fig-2 and right-click and select “Name a Range…” option.

Fig-2 Create a named Range

Fig-3 shows a pop-up box where you can apply and provide a name for that range selection.

Fig-3 Range name for Contacts

Similarly repeat this for Sales Order recordset as shown in Fig-4.

Fig-4 Range name for Sales Order

Now you can see 2 named ranges Contacts & Sales in the dropdown in Fig-5.

Fig-5 Check both the Named ranges

Now we are ready to Import the data. As shown in Fig-6 the Import/Export wizard detects the named ranges and explicitly shows them as Source among other 3 sheets of your Excel file. Simply select both of them and make the required changes as you do for Sheets and click the Next button.

Fig-6 Named ranges while Import

Fig-7 Shows both the tables created & data loaded in SQL Server.

Fig-7 Sources getting copied into SQL tables

Now check the records and match them with the Excel sheet, as shown in Fig-8

Fig-8 Check the tables in SQL Server finally

Wow!!! that was simple.

I was asked this question in an SQL interview and I didn’t knew the answer, obviously you don’t need to know everything… but you should. Discussed this question on MSDN TSQL forum and got the suggestion, thus the blog post. Link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cf849418-d18f-4b7a-99eb-dbfed6269603/#778c0e99-368a-40f2-b9d4-b747c1754853