Import data into SQL Server (without any programming)

Now we already installed SQL 2008 R2 Express edition on our machine; so we can import data into our SQL server.  In this blog, I am going to:

• Create a database to store data
• Import one Excel worksheet (Import and Export Wizard) and
• Import one Access database (Upsizing Wizard)

I am going to use two different approaches to import Excel and Access data just to illustrate that there are many ways to import data.   I have used the Import and Export Wizard to import data from Access, Excel, Oracle, text file… etc and it is my recommended tool to import data into SQL server.The most wonderful news is that both tools require NO programming at all.

Creating Database

Before we start importing data into SQL Server, let’s create a database first.  We are going to name it Demo.

• Start SQL Server Management Studio under Microsoft SQL Server 2008 R2.

• Enter .\SqlExpress in Server name: and click Connect button.

• Microsoft SQL Server Management Studio is now launched

• Right Click on Databases and Select New Database…

• New Database dialogue is displayed.
• Enter Demo in Database name:
• Change the Initial Size (MB) to 50 and 5 for Demo and Demo_log respectively.
• Click OK button.

• Demo database is ready to use.

Import Excel Into SQL Server

We are going to use the Import and Export Wizard from SQL Server; it is a very powerful tool that we can use it to import and export a wide range of data files.  We are going to use it to import Excel.

• Start Import and Export Data (32-bit) under Microsoft SQL Server 2008 R2

• SQL Server Import and Export Wizard is launched, click Next button

• Select Microsoft Excel as Data source
• Enter the location of Excel file in Excel file path:
• Click Next button.

• Choose SQL Server Native Client 10.0 as Destination
• Enter M1330\SQLExpress in Server name.
• Select Demo as Database
• Click Next button.

• Select Copy data from one or more tables or views
• Click Next button.

• Select ‘Sheet 1$’
• Click Edit Mappings…

• Import and Export Wizard would automatically create the table structure for us but we can modify it.
• Click OK button to return to Wizard.
• Click Next button.

• Import and Export Wizard now created a package for us, and we can run it immediately.
• Click Next button.

• Click Finish button.

• Click Close button to close the status dialogue.

• Let’s go back to Microsoft SQL Server Management Studio
• We noticed that there was a new table “dbo.Sheet1$.

• Let’s rename it to ContosoStore

• Table name is changed to ContosoStore.

• Let’s look at the content of the table.

• A SQL statement is generated for us and we can see the result dataset.

Import  Access to SQL Server

We can continue to use Import and Export Wizard to import Access data to SQL server but instead, I would like to show you another tool.

ContosoSales is a simple Access database that only has 6 tables.  The big one is FactSales which has about 3.9 million transactions.

We are going to use Microsoft Access upsizing wizard to migrate Access database into SQL server.

• We select Use existing database
• Click Next button.

• Select Machine Data Source
• Click New… button (This is only one time setup and we can reuse the Machine Data Source in future)

• Select User Data Source (Applies to this machine only)
• Click Next button.

• Select SQL Server
• Click Next button.

• Click Finish button.

• Select With Integrated Windows authentication.
• Click Next button.

• Enter SQL Demo for Name.
• Enter .\SQLExpress for Server.
• Click Next button.

• Select With Windows NT authentication using the network login ID.
• Click Next button.

• Click Change the default database to:
• Enter Demo
• Click Next button.

• Click Finish button.

• Click OK button.

• Click OK button.

• Select Use existing database
• Click Next button.

• Click >> button
• Click Next button.

• Click Next button.

• Click Next button.

• Click Finish button.

• Upsizing would show us a status bar.

• When Upsizing Wizard is completed, we can go back to Microsoft SQL Server Management Studio to confirm if the Access data is successfully migrated to SQL Server.
• We would see six new tables were already added to our Demo database at SQL Server.

Conclusion

Both methods require no programming.  You don’t have to be an IT guru to learn how to import data to SQL server; especially Import and Export Wizard.  Once we create the reusable data package, then we can use the standalone package without the Wizard.  We can even schedule the data package to automatically import the data into SQL Server.

One of the huge advantage of SQL server is its ability to process huge amount of data.  It is very challenging to handle 4 million records even with Excel 2010 64 bit version.  SQL Server also allows concurrent users to analyze data in a controlled and robust environment.

In my next blog, I am going to show you how you can use Excel to analyze data from SQL Server.

• • Create Excel Pivot Table from SQL Server
  • Import data into SQL Server
  • SQL Server 2008 R2 Installation

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!