Copying table from one SQL Server to another

Question

I'm trying to create a copy of a table (no data, just the schema) using SQL Server Management Objects (SMO), Transfer class. The only thing I haven't figured out is how to specify what server to copy to, when the servers are on different hosts. In my case, I want to copy from 10.1.2.x to 10.1.2.y. Is there any way to specify this, or does this class not support it?

Perhaps there are better C# solutions?

static void CreateTableFromTable(string fromConnection, string toConnection, string dbName, string tablename, bool copyData = false)
{
    Server fromServer = new Server(new ServerConnection(new SqlConnection(fromConnection)));
    Database db = fromServer.Databases[dbName];

    Transfer transfer = new Transfer(db);
    transfer.CopyAllObjects = false;
    transfer.DropDestinationObjectsFirst = false;
    transfer.CopySchema = false;   //Database schema? Or Table schema? I DO NOT want to overwrite the db schema
    transfer.CopyData = copyData;
    transfer.DestinationServer = "?";
    transfer.DestinationDatabase = dbName;
    transfer.Options.IncludeIfNotExists = true;
    transfer.ObjectList.Add(db.Tables[tablename]);

    transfer.TransferData();
}

score 1 · Answer 1 · answered Jun 18 '13 at 09:42

1

Have you tried import and export data wizard even importing with table data ,command line or GUI in SQL server 2005/8 and Mysql /MysqlWorkbench.

answered Jun 18 '13 at 09:42

Develop4Life

7,581
8
58
76

score 0 · Answer 2 · answered Oct 03 '13 at 01:05

I'm not sure if you found another solution - or got this one working. If you did not the SMO Scripter object might be worth a look.

This MSDN example could be helpful. You could script the tables and dependencies you want and then open a connection to the destination database and execute the scripts.

static void Main(string[] args)
{
    Server sourceServer = new Server("server");
    String dbName = "database"; 

    // Connect to the local, default instance of SQL Server. 

    // Reference the database.  
    Database db = sourceServer.Databases[dbName];

    // Define a Scripter object and set the required scripting options. 
    Scripter scripter = new Scripter(sourceServer);
    scripter.Options.ScriptDrops = false;
    scripter.Options.WithDependencies = true;
    scripter.Options.Indexes = true;   // To include indexes
    scripter.Options.DriAllConstraints = true;   // to include referential constraints in the script

    // Iterate through the tables in database and script each one. Display the script.   
    foreach (Table tb in db.Tables)
    {
        // check if the table is not a system table
        if (tb.IsSystemObject == false)
        {
            Console.WriteLine("-- Scripting for table " + tb.Name);

            // Generating script for table tb
            System.Collections.Specialized.StringCollection sc = scripter.Script(new Urn[] { tb.Urn });
            foreach (string st in sc)
            {
                //ado.net to destination 
                Console.WriteLine(st);//SqlCommand.ExecuteNonQuery();
            }
            Console.WriteLine("--");
        }
    }
}

score -2 · Answer 3 · answered Jun 12 '13 at 10:53

-2

Did you try to use SELECT ... INTO statement?

For example:

SELECT * INTO DestDatabase.TableName FROM SourceDatabase.TableName

If you don't want to copy data, just add a condition which will be return nothing, ex: WHERE Id = 0

answered Jun 12 '13 at 10:53

Tu Tran

1,957
1
27
50

And how would you specify the IP addresses of the servers containing the databases? – SamiHuutoniemi Jun 12 '13 at 11:18
Just place it on the – Tu Tran Jun 13 '13 at 02:04
How? It's not like i can type SELECT * INTO DestDatabase.TableName FROM 10.1.2.1.SourceDatabase.TableName – SamiHuutoniemi Jun 13 '13 at 10:06
Use the [] for it. For ex: [10.1.2.1].[DB].[Table] – Tu Tran Jun 13 '13 at 10:07

Copying table from one SQL Server to another

3 Answers3