Copy table to a different database on a different SQL Server

Question

I would like to copy a table from one database to another  I know you can easily do the following if the databases are on the same SQL Server   SELECT   INTO NewTable FROM existingdb dbo existingtable    Is there any easy way to do this if the databases are on two different SQL Servers  without having to loop through every record in the original table and insert it into the new table   Also  this needs to be done in code  outside of SQL Server Management Studio

User · Answer

Generate the scripts   Generate a script to create the table then generate a script to insert the data   check-out SP  Genereate Inserts for generating the data insert script

User · Answer

Microsoft SQL Server Database Publishing Wizard will generate all the necessary insert statements  and optionally schema information as well if you need that   http   www microsoft com downloads details aspx familyid 56E5B1C5-BF17-42E0-A410-371A838E570A

User · Answer

SQL Server 2012  provides another way to generate script for the SQL Server databases with its objects and data  This script can be used to copy the tables    schema and data from the source database to the destination one in our case    Using the SQL Server Management Studio  right-click on the source database from the object explorer  then from Tasks choose Generate Scripts   In the Choose objects window  choose Select Specific Database Objects to specify the tables that you will generate script for  then choose the tables by ticking beside each one of it  Click Next   In the Set Scripting Options window  specify the path where you will save the generated script file  and click Advanced   From the appeared Advanced Scripting Options window  specify Schema and Data as Types of Data to Script  You can decide from here if you want to script the indexes and keys in your tables  Click OK   Getting back to the Advanced Scripting Options window  click Next  Review the Summary window and click Next   You can monitor the progress from the Save or Publish Scripts window  If there is no error click Finish and you will find the script file in the specified path     SQL Scripting method is useful to generate one single script for the tables    schema and data  including the indexes and keys  But again this method doesn   t generate the tables    creation script in the correct order if there are relations between the tables

User · Answer

If it   s only copying tables then linked servers will work fine or creating scripts but if secondary table already contains some data then I   d suggest using some third party comparison tool    I   m using Apex Diff but there are also a lot of other tools out there such as those from Red Gate or Dev Art      Third party tools are not necessary of course and you can do everything natively it   s just more convenient  Even if you   re on a tight budget you can use these in trial mode to get things done      Here is a good thread on similar topic with a lot more examples on how to do this in pure sql

User · Answer

Create the database  with Script Database as    CREATE To  Within SSMS on the source server  use the export wizard with the destination server database as the destination    Source instance   YourDatabase   Tasks   Export data Data Soure   SQL Server Native Client   Validate enter Server  amp  Database  Destination   SQL Server Native Client   Validate enter Server  amp  Database  Follow through wizard

User · Answer

Yes  add a linked server entry  and use select into using the four part db object naming convention    Example   SELECT   INTO targetTable  FROM  sourceserver   sourcedatabase   dbo   sourceTable

[sql-server] Copy table to a different database on a different SQL Server

Examples related to sql-server