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.
