Export table data from one SQL Server to another

Question

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   SELECT    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

User · Answer

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; 

xfr.ObjectList.Add(dbSource.Tables[tableToTransfer,transferringTableSchema]);
xfr.TransferData();

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

User · Answer

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

User · Answer

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

User · Answer

Just to show yet another option  for SQL Server 2008 and above     right-click on Database -  select  Tasks  -  select  Generate Scripts  Select specific database objects you want to copy  Let s say one or more tables  Click Next Click Advanced and scroll down to  Types of Data to script  and choose  Schema and Data   Click OK Choose where to save generated script and proceed by clicking Next

User · Answer

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

User · Answer

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

User · Answer

Try this    create your table on the target server using your scripts from the Script Table As   Create Script step on the target server  you can then issue a T-SQL statement   INSERT INTO dbo YourTableNameHere    SELECT      FROM  SourceServer   SourceDatabase  dbo YourTableNameHere    This should work just fine

User · Answer

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  lt database name gt   lt schema name gt   lt table name gt  OUT C  FILE TXT -c -t -T -S  lt server name   instance name  gt  -U  lt username gt  -P  lt Password gt     To import the table data from a text file on target server   bcp  lt database name gt   lt schema name gt   lt table name gt  IN C  FILE TXT -c -t -T -S  lt server name   instance name  gt  -U  lt username gt  -P  lt Password gt

User · Answer

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

User · Answer

For copying data from source to destination   use  lt DestinationDatabase gt  select   into  lt DestinationTable gt  from  lt SourceDataBase gt  dbo  lt SourceTable gt

User · Answer

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

User · Answer

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  SELECT   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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to export