[sql] Export table data from one SQL Server to another

I have two SQL Servers (both 2005 version).

I want to migrate several tables from one to another.

I have tried:

  • On source server I have right clicked on the database, selected Tasks/Generate scripts. The problem is that under Table/View options there is no Script data option.

  • Then I used Script Table As/Create script to generate SQL files in order to create the tables on my destination server. But I still need all the data.

Then I tried using:

INTO [destination server].[destination database].[dbo].[destination table] 
FROM [source server].[source database].[dbo].[source table]

But I get the error:

Object contains more than the maximum number of prefixes. Maximum is 2.

Can someone please point me to the right solution to my problem?

This question is related to sql sql-server sql-server-2005 export

The answer is

For copying data from source to destination:

use <DestinationDatabase>
select * into <DestinationTable> from <SourceDataBase>.dbo.<SourceTable>

It can be done through "Import/Export Data..." in SQL Server Management Studio

If you don't have permission to link servers, here are the steps to import a table from one server to another using Sql Server Import/Export Wizard:

  • Right click on the source database you want to copy from.
  • Select Tasks - Export Data.
  • Select Sql Server Native Client in the data source.
  • Select your authentication type (Sql Server or Windows authentication).
  • Select the source database.
  • Next, choose the Destination: Sql Server Native Client
  • Type in your Server Name (the server you want to copy the table to).
  • Select your authentication type (Sql Server or Windows authentication).
  • Select the destination database.
  • Select Copy data.
  • Select your table from the list.
  • Hit Next, Select Run immediately, or optionally, you can also save the package to a file or Sql Server if you want to run it later.
  • Finish

If the tables are already created using the scripts, then there is another way to copy the data is by using BCP command to copy all the data from your source server to your destination server

To export the table data into a text file on source server:

bcp <database name>.<schema name>.<table name> OUT C:\FILE.TXT -c -t -T -S <server_name[ \instance_name]> -U <username> -P <Password> 

To import the table data from a text file on target server:

bcp <database name>.<schema name>.<table name> IN C:\FILE.TXT -c -t -T -S <server_name[ \instance_name]> -U <username> -P <Password>

Try using the SQL Server Import and Export Wizard (under Tasks -> Export Data).

It offers to create the tables in the destination database. Whereas, as you've seen, the scripting wizard can only create the table structure.

Just to show yet another option (for SQL Server 2008 and above):

  1. right-click on Database -> select 'Tasks' -> select 'Generate Scripts'
  2. Select specific database objects you want to copy. Let's say one or more tables. Click Next
  3. Click Advanced and scroll down to 'Types of Data to script' and choose 'Schema and Data'. Click OK
  4. Choose where to save generated script and proceed by clicking Next

Yet another option if you have it available: c# .net. In particular, the Microsoft.SqlServer.Management.Smo namespace.

I use code similar to the following in a Script Component of one of my SSIS packages.

var tableToTransfer = "someTable";
var transferringTableSchema = "dbo";

var srvSource = new Server("sourceServer");
var dbSource = srvSource.Databases["sourceDB"];

var srvDestination = new Server("destinationServer"); 
var dbDestination = srvDestination.Databases["destinationDB"];

var xfr = 
    new Transfer(dbSource) {
        DestinationServer = srvDestination.Name,
        DestinationDatabase = dbDestination.Name,
        CopyAllObjects = false,
        DestinationLoginSecure = true,
        DropDestinationObjectsFirst = true,
        CopyData = true

xfr.Options.ContinueScriptingOnError = false; 
xfr.Options.WithDependencies = false; 


I think I had to explicitly search for and add the Microsoft.SqlServer.Smo library to the references. But outside of that, this has been working out for me.

Update: The namespace and libraries were more complicated than I remembered.

For libraries, add references to:

  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.SmoExtended.dll
  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll

For the Namespaces, add:

  • Microsoft.SqlServer.Management.Common
  • Microsoft.SqlServer.Management.Smo

There is script table option in Tasks/Generate scripts! I also missed it at beginning! But you can generate insert scripts there (very nice feature, but in very un-intuitive place).

When you get to step "Set Scripting Options" go to "Advanced" tab.

Steps described here (pictures can understand, but i do write in latvian there).

This is somewhat a go around solution but it worked for me I hope it works for this problem for others as well:

You can run the select SQL query on the table that you want to export and save the result as .xls in you drive.

Now create the table you want to add data with all the columns and indexes. This can be easily done with the right click on the actual table and selecting Create To script option.

Now you can right click on the DB where you want to add you table and select the Tasks>Import .

Import Export wizard opens and select next.Select the Microsoft Excel as input Data source and then browse and select the .xls file you have saved earlier.

Now select the destination server and also the destination table we have created already.

Note:If there is any identity based field, in the destination table you might want to remove the identity property as this data will also be inserted . So if you had this one as Identity property only then it would error out the import process.

Now hit next and hit finish and it will show you how many records are being imported and return success if no errors occur.

You can't choose a source/destination server.

If the databases are on the same server you can do this:

If the columns of the table are equal (including order!) then you can do this:

INSERT INTO [destination database].[dbo].[destination table]
FROM [source database].[dbo].[source table]

If you want to do this once you can backup/restore the source database. If you need to do this more often I recommend you start a SSIS project where you define source database (there you can choose any connection on any server) and create a project where you move your data there. See more information here: http://msdn.microsoft.com/en-us/library/ms169917%28v=sql.105%29.aspx

Just for the kicks.

Since I wasnt able to create linked server and since just connecting to production server was not enough to use INSERT INTO i did the following:

  • created a backup of production server database
  • restored the database on my test server
  • executed the insert into statements

Its a backdoor solution, but since i had problems it worked for me.

Since i have created empty tables using SCRIPT TABLE AS / CREATE in order to transfer all the keys and indexes I couldnt use SELECT INTO. SELECT INTO only works if the tables do not exist on the destination location but it does not copy keys and indexes, so you have to do that manualy. The downside of using INSERT INTO statement is that you have to manualy provide with all the column names, plus it might give you some problems if some foreign key constraints fail.

Thanks to all anwsers, there are some great solutions but i have decided to accept marc_s anwser.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to export

Export multiple classes in ES6 modules Why Is `Export Default Const` invalid? How to properly export an ES6 class in Node 4? ES6 export all values from object Export a list into a CSV or TXT file in R How to export database schema in Oracle to a dump file Excel VBA to Export Selected Sheets to PDF How to export all data from table to an insertable sql format? Export pictures from excel file into jpg using VBA -bash: export: `=': not a valid identifier