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:

    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.

image

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

image

  • Microsoft SQL Server Management Studio is now launched

image

  • Right Click on Databases and Select New Database…

image

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

image

  • Demo database is ready to use.

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

  • Click Finish button.

image

  • Click Close button to close the status dialogue.

image

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

image

  • Let’s rename it to ContosoStore

image

  • Table name is changed to ContosoStore.

image

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

image

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

image

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.

image

  • We select Use existing database
  • Click Next button.

image

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

image

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

image

  • Select SQL Server
  • Click Next button.

image

  • Click Finish button.

image

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

image

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

image

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

image

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

image

  • Click Finish button.

image

  • Click OK button.

image

  • Click OK button.

image

  • Select Use existing database
  • Click Next button.

image

  • Click >> button
  • Click Next button.

image

  • Click Next button.

image

  • Click Next button.

image

  • Click Finish button.

image

  • Upsizing would show us a status bar.

image

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

image

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.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

7 Responses to Import data into SQL Server (without any programming)

  1. Pingback: Create Excel Pivot Table from SQL Server « Technologies and your business

  2. Pingback: SQL Server 2008 R2 Express Installation « Technologies and your business

  3. Pingback: 2010 in review « Technologies and your business

  4. Really appreciate the effort behind this site. It is perfectly designed. I can have a glance easily because of the eye catching background. I almost daily move my mouse scroll here. Well done.

  5. Tremendous work with this blog. It was really convenient from a reader’s perspective. Lots of information is there. I love this place to visit. It has diversified contents.

  6. Hey there! Someone in my Myspace group shared this site with us so I came to check it out.
    I’m definitely loving the information. I’m bookmarking and will be tweeting this to my followers!

    Outstanding blog and superb design.

  7. such_per says:

    Thanks a lot ! ! I was finding ho to import data into MS Sql……
    I owe ya a pint!!!!
    🙂 🙂 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: