Move tables between two SQL Server databases using C#

Question

Now this question may have been asked before but none of the answers I've found are satisfactory. Simply what I'm trying to do is move one table (including data) between databases under one instance of SQL Server Express.

I have tried creating a copy of the table and deleting the old one, but generating scripts using C# is a small nightmare and I keep running into syntax issues left right and centre. I've tried the following code to simply generate scripts but I get syntax errors:

            string connectionString = "<myconnectionstring>";
            SqlConnection conn = new SqlConnection(connectionString);
            string script = dbscript;
            string dbscript = ScriptDatabase();
            Server server = new Server(new ServerConnection(conn));
            server.ConnectionContext.ExecuteNonQuery(script);

where ScriptDatabase() is as follows:

public string ScriptDatabase()
        {
            string connectionString = "<myconnectionstring>";
            SqlConnection conn = new SqlConnection(connectionString);

            Server server = new Server(new ServerConnection(conn));
            var sb = new StringBuilder();

            var database = server.Databases["dbname"];

            var scripter = new Scripter(server);
            scripter.Options.ScriptData = true;
            scripter.Options.ScriptDrops = false;
            scripter.Options.IncludeHeaders = true;
            //And so on ....

            Microsoft.SqlServer.Management.Smo.Table t = database.Tables["tablename"];

            var smoObjects = new Urn[1];

                smoObjects[0] = t.Urn;
                if (t.IsSystemObject == false)
                {

                    foreach (var st in scripter.EnumScript(smoObjects))
                    {
                        sb.Append(st);
                    }
                }
            return sb.ToString();
        }

The other big issue I have is how do I generate scripts for one table in a database so that the opening USE [CurrentDB] statement is generated as USE [OtherDB]?

Thanks guys

Use [SMO](http://msdn.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.aspx) especially the [Transfer class](http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx) — Hamlet Hakobyan, Oct 04 '14 at 10:40

score 1 · Answer 1 · answered Oct 05 '14 at 11:24

Why wouldn't you just do it in SQL?

Set Nocount On
Select * Into destinationDb.dbo.destinationTable From sourceDB.dbo.sourceTable
drop table sourceDB.dbo.sourceTable

This copies the entire source table (schema and data) into the destination database, and then drops the source. No "Use" statements required, and it's pretty fast.

Is this what you're trying to do? Obviously you'd just execute this via C# using a SQL Command and ExecuteNotQuery(), and you should be good to go.

Move tables between two SQL Server databases using C#

1 Answers1