将一个表的所有行复制到另一个表

Question

我有两个数据库，一个是MySQL，另一个是SQL Server，我想在SQL Server中创建一张表，并将MySQL中表中的所有行复制到SQL Server的新表中。

我可以使用以下代码在SQL Server中创建与MySQL相同的表：

List<String> TableNames = new List<string>();
{ 
    IDataReader reader= 
        ExecuteReader("SELECT Table_Name FROM information_schema.tables WHERE table_name LIKE 'mavara%'",MySql);
    while (reader.Read()) {
        TableNames.Add(reader[0].ToString());
    }
    reader.Close();
}

foreach (string TableName in TableNames) {
    IDataReader reader = 
        ExecuteReader("SELECT Column_Name,IS_NULLABLE,DATA_TYPE FROM information_schema.columns where TABLE_Name='" + TableName + "'",MySql);
    List<string[]> Columns = new List<string[]>();
    while (reader.Read()) { 
        string[] column = new string[3];
        column[0] = reader[0].ToString();
        column[1] = reader[1].ToString();
        column[2] = reader[2].ToString();
        Columns.Add(column);
    }

    reader.Close();

    // create table
    string  queryCreatTables=  "CREATE TABLE [dbo].[" + TableName + "](\n";
    foreach(string[] cols in Columns)
    {
        queryCreatTables +="["+ cols[0] + "] " + cols[2] + " ";
        if (cols[1] == "NO")
            queryCreatTables += "NOT NULL";
        // else
        //   queryCreatTables += "NULL";
        queryCreatTables += " ,\n ";
    }
    queryCreatTables += ")";

    System.Data.SqlClient.SqlCommand smd = 
        new System.Data.SqlClient.SqlCommand(queryCreatTables, MsSql);
    System.Data.SqlClient.SqlDataReader sreader = smd.ExecuteReader();
    sreader.Close();

但我有一个问题，无法将一张表中的行复制到另一张表中。

对于 select 查询，我使用 IDataReader，但我不知道如何将行插入到另一个表中。

- atabrizi

---

这听起来更适合在DBMS层面处理。或者这是经常进行的迁移，需要一个程序来自动化该过程吗？ - Jason Larke

我需要一个程序来处理这个。 - atabrizi

Answer 1

想要将一张表中的行插入到另一张表中，请参考以下示例查询：

    INSERT INTO Store_Information (store_name, Sales, Date)
    SELECT store_name, sum(Sales), Date
     FROM Sales_Information

- Rubin Porwal

Answer 2

算法如下：

1. For each table in source database
2.    Get a list of columns for that table
3.    Create table in destination database
4.    SELECT * FROM the table in source
5.    For each row in data
6.       Generate INSERT statement and execute on destination database

您需要的列信息包括名称、类型、长度等。

然后，通过对列进行迭代，生成插入语句。

var insertStatement = "INSERT INTO " + tableName + " VALUES( ";
foreach( var column in columns )
    insertStatement += "@" + column.Name + ",";
insertStatement[insertStatement.Length-1] = ')';

var command = new SqlCommand( insertStatement, MsSql );

// iterate over the columns again, but this time set values to the parameters
foreach( var column in columns )
   command.Parameters.AddWithValue( "@"+column.Name, currentRow[column.Name] );

- Alex

Answer 3

但我有一个问题，无法将一张表的行复制到另一张表中。 您可以使用 SqlDataAdapter.UpdateBatchSize 来执行批量更新/插入数据操作，从而将数据从一张表复制到另一张表。在使用类似下面的方式获取第一个 MYSQL 表中的所有记录后进行操作：

//Get the rows from the first mysql table as you did in your question
DataTable mysqlfirstTableRowsTobeCopied = GetDataTableFromMySQLTable();

然后您需要创建一条命令文本，执行INSERT语句，类似于：

cmd.CommandText = "INSERT INTO TableName Column_Name, ... VALUES(...)";

或者您可以使用存储过程：

CREATE PROCEDURE sp_BatchInsert ( @ColumnName VARCHAR(20), ... ) 
AS 
BEGIN 
            INSERT INTO TableName VALUES ( @ColumnNamne, ...); 
END

然后：

DataTable mysqlfirstTableRowsTobeCopied = GetDataTableFromMySQLTable();

SqlConnection conn = new SqlConnection("Your connection String");
SqlCommand cmd = new SqlCommand("sp_BatchInsert", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.UpdatedRowSource = UpdateRowSource.None;

// Set the Parameter with appropriate Source Column Name
cmd.Parameters.Add("@ColumnName", SqlDbType.Varchar, 50,
    mysqlfirstTableRowsTobeCopied.Columns[0].ColumnName);   
...
SqlDataAdapter adpt = new SqlDataAdapter();
adpt.InsertCommand = cmd;
// Specify the number of records to be Inserted/Updated in one go. Default is 1.
adpt.UpdateBatchSize = 20;

conn.Open();
int recordsInserted = adpt.Update(mysqlfirstTableRowsTobeCopied);   
conn.Close();

以下代码实际上是从codeproject的一篇完整教程中引用的，您可以参考该教程获取更多信息：

• 多种方式进行多个插入操作

- Mahmoud Gamal

---

我读了这篇文章，但不知道要插入查询的列名和类型。 - atabrizi

@atabrizi，请参考MS Sql Server Management Studio来确定其名称和数据类型，然后选择与该类型相对应的ADO.NET数据类型。 - Mahmoud Gamal

如果您阅读我的代码，您会看到我从MySQL表创建表格，但我不知道列的名称。而且我有170个表格。我需要一个程序来处理所有这些表格。 - atabrizi

@atabrizi，您可以从您的mysql数据库中获取列名和数据类型。但是，您是否想为这170个表做同样的事情？ - Mahmoud Gamal

Answer 4

假设您已经有一个数据表，其中包含要与另一个表合并的行...

有两种方法可以做到这一点...再次假设您已经选择了数据并将其放入了新表中。

            oldTable.Load(newTable.CreateDataReader());

            oldTable.Merge(newTable);

- JasonG

Answer 5

通常这是可以直接在SQL中完成的： INSERT INTO table FROM SELECT * FROM othertable；

- pagid

---

我的其中一张表在SQL Server中，另一张表在MySQL中，我无法使用这段代码。 - atabrizi

Answer 6

将所有记录插入新表（如果第二个表不存在）

选择* into New_Table_Name from Old_Table_Name;

将所有记录插入第二个表（如果第二个表存在，但表结构应相同）

Insert into Second_Table_Name from(Select * from First_Table_Name);

- Swapnil Ganjegaonkar

Answer 7

以防有人需要，我找到了一种在C#中使用SqlDataAdapter轻松实现的方法。它可以自动检测表的结构，因此您不必枚举所有列或担心表结构的更改。然后将数据批量插入目标表。

只要两个表具有相同的结构，这个方法就可以工作。（例如，从生产表复制到空的开发表。）

using(SqlConnection sqlConn = new SqlConnection(connectionStringFromDatabase))
    {
      sqlConn.Open();
      using(var adapter = new SqlDataAdapter(String.Format("SELECT * FROM [{0}].[{1}]", schemaName, tableName), sqlConn))
      {
        adapter.Fill(table);
      };
    }

    using(SqlConnection sqlConn = new SqlConnection(connectionStringDestination))
    {
      sqlConn.Open();
      // perform bulk insert
      using(var bulk = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepIdentity|SqlBulkCopyOptions.KeepNulls, null))
      {
        foreach(DataColumn column in table.Columns)
        {
          bulk.ColumnMappings.Add(column.ColumnName, column.ColumnName);
        }
        bulk.DestinationTableName = String.Format("[{0}].[{1}]", schemaName, tableName);
        bulk.WriteToServer(table);
      }
    }

- Danielle Paquette-Harvey