By: Ronald Valverde.
While working with SQL Server databases, you will find that sometimes you want to replicate a certain set of records or even one or more tables from a production setting into a different environment in order to run some tests or to reproduce a bug.
By all means you should always try to get a backup of the database that you want to replicate, but if that is not an option either because you are working in a remote environment where you don’t have enough permission to run the backup or because the resulting file is too large and it would take a huge time to complete the transfer, a good option is to export just the excerpt of records that you want to analyze.
To this end, we can use the SQL Server Import and Export Wizard.
First we need to launch the wizard from SQL Server Management Studio by right clicking over the database we want to work with, then from the context menu open the Tasks submenu and select the Export Data… option.
This will bring up the wizard, in the first screen we are asked to choose a data source; we should do so by providing the connection parameters to the database we want to work with.
The next screen will ask for a Destination, there are many options available but in order to generate a smaller output you should use a Flat File, after selecting the destination you should specify the path to the target file. You can also enable the column names checkbox so you can analyze the records with other tools.
The next screen let us choose between copying tables and querying them; for this example we will select the copy option.
The next step is to choose the table that you want to copy, also this screen enable us to define the separator character that will be used in the flat file, the default separator is the comma character, but from previous experiences we know that a comma could be used inside a description or address field; our advice is to use the vertical bar as the separator.
In the next screen we will just hit the finish button, a summary with the settings we selected will be displayed and from there after pressing Finish again, we can start the export process.
The progress window will be displayed and will notify us if there are any problems while performing the operation, otherwise it will complete successfully.
The output will be a text file that can be transferred easily.
Now we should bring up the wizard in the target server and this time we will import the data instead of exporting it.
To do this on the first screen we must select the Flat File option and browse for the file that was generated by the Export process.
Since the flat file doesn’t specify the data types that must be used for each field the wizard will assume that the type should be string for all the cases.
If you try to proceed with the import of the file like this it is most likely that you will get an import error.
You could adjust the data type for each field manually but you can also make use of the Suggest Types button.
This feature will try to identify the field data types automatically by reviewing the contents of the flat file, to do this it will ask you to specify a sample size, it is advisable to use the maximum number of rows of 10000.
In order to make sure you are importing the fields correctly please verify that for those fields that have enabled the OutputColumnWidth property it has a proper value for the length of your data.
In the next screen you have to specify the connection setting for your target database.
After that we will be able to select the target table were we want to import the data:
The next screen will display if there are fields with risk of data lose, if that is the case please make sure the field data types are suitable.
Then proceed to the next screen and click finish to start the import process, if the configurations with the field data types are correct the process will complete successfully.