This post is a visual guide about one of the most powerful features of Microsoft SQL Server Management Studio (SSMS) 2008: Import/Export database data.
These tasks are useful for example when you need to debug an application with real world/production data to find possible bugs in your code. You can import the production database data to your local development database and find what’s causing problems. These tasks can be used to create a protection layer because you won’t be working “directly” with production data.
As you see, if I right click a database in SSMS I get access to this rich menu full of interesting options that deserve a careful study:
Looking at the above picture you see in Object Explorer that I’m connected to two SQL database servers. The first one is a remote SQL Server (version 10.0.2775) and the other one is my local SQL Server Express (version 10.50.1617) instance.
With this configuration I have the perfect scenario to visually show you how the Import/Export Data… menu options work.
In this blog post I’ll execute the Export Data task. This allows me to test my web application using my local SQL Server instance with real world/production data.
The Import Data… task does practically the same thing but in reverse, logically. I’ll let the execution of the Import task to you dear reader!
OK… If I proceed and select the Export Data… menu option I’ll be able to bring new data to the database that is located on my machine. Data comes from the remote/production server to my local copy of the same database.
After clicking Export Data…, SQL Server Import and Export Wizard pops up. I then select the Data source/Server name (from which Data source type and Server I want to get the data to be imported). In this case, the data comes from the remote SQL server. I use SQL Server Authentication, fill the user name and password and select the Database that’ll provide the data. In Server name field I used the IP address of my remote server.
Now is the time to choose the Destination. In this case it’s my local SQL Server instance. LENIEL-PC is my Windows 7 machine name. SQLEXPRESS is the default SQL Express server name. I also selected the Database I want to receive the imported data. As you see both databases (source and destination) have the same name to make things easy.
The wizard then asks if I want to copy data directly from table to table or if I want to write a custom SQL query to shape the data according to specific needs. I’ll select the first option to keep things simple.
In the next screen we get a beautiful list of available tables and views. I’ve selected two tables that I want to copy.
If you click the Edit Mappings… button (which applies to each table selected in the grid), I can also specify if I want to delete rows before importing data or if I want to append rows to the destination table. I’ll append rows because I have already erased both tables (exams and files) on my local database.
As I’m using the free SQL Server Express Edition, the following screen has an interesting information:
In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.
This is telling me that I’ll have to go over the wizard (#7 steps) again if I happen to execute this very import process. Really annoying… 
Next button once more (the last one hallelujah) and we’re ready to go. It’s just a matter of reviewing what’ll be done and pressing the finish button.
After clicking Finish I got this last window:
Hope this simple and visual guide helps you understand how the data Import and Export processes work when dealing with a SQL Server database inside SSMS.
Free tools
Microsoft SQL Server Express Edition
Microsoft SQL Server 2008 Management Studio Express (SSMSE)
