Transport SQL Data from a server to another server

Sometimes, you may want to copy data from a table of a server to another table to another server which is not connected to source server. What will you do?

 

In fact, it is not much a deal. It can be easily done with the help of "Import and Export Data" utility provided by SQL Server 2000.

 

Just open the utility, select the source data source (Microsoft OLE DB Provider), source SQL Server name and database name.

 

Then, select the destination data source as Microsoft Excel (any version), Access or Text file.

 

Then, select "Copy Tables" option and click "Next".

Finally, select the table(s) you want to copy. If you use Text file as destination, then you can choose only one table from source to export.

 

The process can be reversed for importing data from exported Text File or Excel. You have to choose the Text file or Excel as source and SQL Server as destination this time.

 

SQL Sever uses bulk insert in background to achieve this. It works fine under normal conditions. For this to work fine, of course, source and destination tables should have same structure or destination tables should have columns of same data type as source tables. The mapping can be done at the time of importing.

 

If you are using table with column of "uniqueidentifier" data type, then it is preferable to use Excel for import export because while using Text file it converts the values to string and creates problem while importing string to column of datatype uniqueidentifier.

 

Under default options, this utility appends new imported rows to destination tables. So, if it contains identity and uniqueidentifier columns, then chances are there that after importing, it contains duplicate identities and uniqueidentifier. However, there are options to delete all rows first or drop and create a new destination table.

 

If the destination tables contain some constraints which prevent from inserting import data, then error is thrown and import fails.

 

Finally, SQL Import and Export Data is an excellent utility to copy raw data from one location to another location not having direct connection.