[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Transferring Data from MS Access 2003 to SQL Server 2008, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
by
Dr. Jayaram Krishnaswamy | July 2009 |
Microsoft
The Export and Import Wizard is an extremely useful tool for transferring data. In fact it is the simplest tool to copy over data from one database to another and to create data transfer packages that can be persisted. Data can be transferred between SQL Servers (between versions of SQL Servers for example) as well as between even two non-Microsoft databases. Both Microsoft and proprietary data source providers are available to connect to many different database products.
In this article by Dr. Jayaram Krishnaswamy, we will be transferring data from an MS Access database to a database on SQLServer 2008. Both the source of data and the destination database are on the same machine, in this case a computer box running Windows XP Pro. There are two versions of the Import and Export Wizard and in this example the 32 bit version is used.
Launching the Import and Export Wizard
Click Start | All Programs| SQL Server 2008 and click on Import and Export Data (32 bit) as shown in the next figure.
This brings up the - "Welcome to the SQL Server Import and Export Wizard”. Make sure you read the explanation on this window.
Connecting to Source and Destination databases
You will connect to the source and destination. In this example the data to be transferred is in a MS Access 2003 database. A table in the Northwind.mdb file available in MS Access will be used for the source. The destination is the 'tempdb' database (or the master database). These are chosen for no particular reason and you can even create a new database on the server at this point.
Connecting to the Source
Click Next. The default window gets displayed as shown. The data source is SQL Server Native Client 10.0.
As we are transferring from MS Access 2003 to SQL Server 2008 we need to use an appropriate data source. Click on the drop-down handle for the data source and choose Microsoft Access as shown in the next figure.
The 'SQL Server Import and Export Wizard' changes to the following. Click on the Browse... button to locate the Northwind.mdb file on your machine also as shown. For Username use the default 'Admin'.
Click the Advanced button. It opens the Data Link properties window as shown. You can test the connection as well as look at other connection related properties using the Connection and Advanced tabs.
Click OK to the Microsoft Data Link message as well as to the Data Link Properties window. Click Next.
Choose Destination
The Choose a Destination page of the wizard gets displayed. The Destination source (SQL Server Native Client 10.0) and the Server name that automatically shows up are defaults. These may be different in your case. Accept them. The authentication information is also correct (the SQL Server is configured for Windows Authentication). Click on the drop-down handle for the Database presently displaying <default> as shown.
Choose tempdb and click on the Next button.
Choosing a table or query for the data to be transferred
You can transfer data in table or tables; view or views by copying them over, or you may create a query to collect the data you want transferred. In this article both options will described.
Copying a table over to destination
The Specify Table Copy or Query page of the wizard gets displayed as shown. First we will copy data from a table. Later we will see how to write a query to do data transfer.
Click Next. The Select Source Tables and Views page gets displayed as shown. When you place a check mark for say, Customers, the Destination column gets an entry for the selection you made. We assume that just this table is transferred. However you can see that you can transfer all of them in one shot.
This window also helps you with two more important things. First you can Preview... the data you are transferring. Secondly, you can edit the mappings, the scheme of how source columns go over to destination.
You can modify columns
Click the Edit Mappings... button. The Column mappings page gets displayed as shown. If you go ahead with this choice by clicking OK, the default action, Create destination table with the information present in this window will be used. You could edit the SQL, that is customize a scheme for the transfer (You may not need all columns for example, you may need a different data type, and for other reasons).
For now accept the default by clicking OK (that is to say, no changes were made for column mappings).
In the Select Source Tables and Views page click Next.
Start moving the data
Now the Save and Run Package page gets displayed. There are two options. You can use the first option if you are in a hurry or you can save the Package as well as run.
Accept the default (only check mark for run). The Complete the Wizard page gets displayed. Showing your choices for source and destination; the column mappings and regarding saving package for later use.
Click Finish. The process of transfer begins immediately as shown reporting the progress.
Transfer is done
When all transfer process is finished you should be seeing the following screen. There were no errors in this case. Depending on what you are transferring you may get some errors which show up as hyperlinked information in the message column.
You are done. If you want to see a report you could click on the drop-down button and pick an item to see such as , View Report, Save report to file, Copy report to clipboard and send report as an email to some one who needs the information. Click Close.
Verifying Transferred data
Open SQL Server 2008 Management Studio and connect to the server by logging-in. Expand Databases node and expand the System Databases node. Open the Tables node in tempdb as shown. You will see that the Column properties of the transferred table dbo.Customers closely match the data you saw earlier before (remember that no column mappings were made).
Use Query to transfer data
We saw at an earlier page of the wizard there were two possibilities of data transfer, copy table(s) or View(s) from source to destination or write a query to transfer data. In this section we will look at the second option.
We go through all the steps for the same source and destination (Master database on SQL Server 2008) as discussed earlier and choose to write a query.
Click Next. This opens the Provide a Source Query of the Wizard as shown.
Type in the SQL Statement shown in the window. You can test the syntax with the Parse button which should verify the validity of the statement.
Close the message by clicking Ok and click on Next on the Provide a query Source page.
You get the Select Source Tables and Views as shown. You can change the query name in the destination column to one of your own. You could also Edit Mappings of column as well as preview the data.
Here the query was changed to SpecialOrders by typing over the default dbo.query.
Click Next. In the window that shows up accept the default to 'RUN immediately' and click Next. Click Finish on the 'Complete the Wizard' page. The processing window shows up indicating a success.
Verify the transfer in the Management studio as shown in the 'master' database.
Summary
This article shows how you may transfer table(s), View(s) or query results to SQL Server 2008 from an MS Access database. Export / Import tool does not have a graphical query designer such as the one you find in the SQL Server Management Studio and you will need to have a statement ready to transfer the data returned by a query. During the transfer of data we did not save a package, but a saved package can be run later.
If you have read this article you may be interested to view :
- MySQL Data Transfer using SQL Server Integration Services (SSIS)
- Displaying SQL Server Data using a Linq Data Source
- Migrating from MS SQL Server 2008 to EnterpriseDB
- Creating a VB.NET application with EnterpriseDB
About the Author :
Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net
Books From Packt
|
|
|
Post new comment