Copying tables among databases

Question

Let's say I want to copy all tables with their complete data from one database to another without specifically knowing detailed information about them(column count, data types...). The user would input a connection string to his database, and all data from it would be copied to an internal DB. I tried to achieve it by using SqlConnection and writing direct T-SQL queries and managed to write a script that creates empty tables in Internal database with correct columns:

string createDestinationTableQuery = "create table " + schemaName + ".[" + tableName + "](";
DataTable ColumnsDT = new DataTable();
string getTableColumnDataQuery = "SELECT * FROM "+originalDBName+".INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'" + tableName +"'";
SqlCommand getTableColumnDataCommand = new SqlCommand(getTableColumnDataQuery, originalCon);
SqlDataAdapter TableDA = new SqlDataAdapter(getTableColumnDataCommand);
TableDA.Fill(ColumnsDT);
for (int x = 0; x < ColumnsDT.Rows.Count; x++)
{
    createDestinationTableQuery += "[" + ColumnsDT.Rows[x].ItemArray[3].ToString() + "] " + "[" + ColumnsDT.Rows[x].ItemArray[7].ToString() + "], ";

}

createDestinationTableQuery = createDestinationTableQuery.Remove(createDestinationTableQuery.Length - 2);
createDestinationTableQuery += " )";

SqlCommand createDestinationTableCommand = new SqlCommand(createDestinationTableQuery, destinationCon);
createDestinationTableCommand.ExecuteNonQuery();
Console.WriteLine("Table " + schemaName + "." + tableName + " created succesfully!");

However, I am struggling with data insertion as the following code simply doesn't work:

DataTable dataTable = new DataTable();

string getTableDataquery = "select * from " + originalTableWithSchema;
SqlCommand getTableDataCommand = new SqlCommand(getTableDataquery, originalCon);
SqlDataAdapter da = new SqlDataAdapter(getTableDataCommand);

da.Fill(dataTable);

for (int x = 0; x < dataTable.Rows.Count; x++)
{
    string insertQuery = "insert into " + schemaName + ".["+tableName+"](" ;
    string values = "VALUES(";

    for (int y = 0; y < dataTable.Columns.Count; y++)
    {
        insertQuery += dataTable.Columns[y].ColumnName + ", ";
        values += dataTable.Rows[x].ItemArray[y].ToString() + ", ";
    }
    insertQuery = insertQuery.Remove(insertQuery.Length - 2);
    insertQuery += " )";
    values = values.Remove(values.Length - 2);
    values += " )";
    insertQuery += " " + values;
    SqlCommand insertCommand = new SqlCommand(insertQuery, destinationCon);
    insertCommand.ExecuteNonQuery();

}

da.Dispose();

How can I correctly Achieve this functionality? I was thinking of maybe scrapping all the code and using SMO instead?

Answer 1

It might be worth thinking about using a linked server. Once a linked server is defined in the destination server, a table can be created and automatically filled with data using a SELECT…INTO statement.

Query executed in destination server database:

SELECT * INTO NewTableName FROM 
SourceServername.SourceDatabasename.dbo.SourceTableName

Answer 2

If you are only looking to copy the data (because you have structure creation already working), then you could use DataTable to hold the data in a non-dbms specific structure, and a DataAdapter to generate the dbms specific insert statements. Here is an excerpt from code I wrote a while ago to copy data from Access to MySQL:

List<string> tableNames = new List<string>();
try
{
    // Open connect to access db
    sourceConn.Open();
    // Build table names list from schema
    foreach (DataRow row in sourceConn.GetSchema("Tables").Select("table_type = 'TABLE'"))
        tableNames.Add(row["table_name"].ToString());                
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    if(sourceConn.State != ConnectionState.Closed)
        sourceConn.Close();
}

foreach (string table in tableNames)
{
    //Get all table data from Access
    string query = string.Format("SELECT * FROM {0}", table);
    DataTable accessTable = new DataTable(table);
    try
    {
        sourceConn.Open();
        System.Data.OleDb.OleDbCommand accessSqlCommand = new System.Data.OleDb.OleDbCommand(query, accessConn);
        System.Data.OleDb.OleDbDataReader reader = (System.Data.OleDb.OleDbDataReader)accessSqlCommand.ExecuteReader();
        // Load all table data into accessTable
        accessTable.Load(reader);
    }
    catch(Exception ex)
    {
        throw ex;
    }
    finally
    {
        if(sourceConn.State != ConnectionState.Closed)
            sourceConn.Close();
    }

    // Import data into MySQL
    accessTable.AcceptChanges();
    // The table should be empty, so set everything as new rows (will be inserted)
    foreach (DataRow row in accessTable.Rows)
        row.SetAdded();

    try
    {
        destConn.Open();
        MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(query, mySqlConn);
        MySql.Data.MySqlClient.MySqlCommandBuilder cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(da);
        da.InsertCommand = cb.GetInsertCommand();

        // Update the destination table 128 rows at a time
        da.UpdateBatchSize = 128;

        // Perform inserts (and capture row counts for output)
        int insertCount = da.Update(accessTable);
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if(destConn.State != ConnectionState.Closed)
            destConn.Close();
    }
}

This could certainly be more efficient, but I wrote it for a quick conversion. Also, since this is copied and pasted you may need to tweak it. Hope it helps.