使用BulkCopy将数据表复制到SqlServer新表或现有表

Question

有没有办法将内存中的数据表(vb.net)及其列(架构)复制到sql server新的或现有的表中？如果向临时表中添加了列，是否可以将新列添加到现有的sql server表中，从而对数据执行BulkCopy操作？

Answer 1

以下是我用来将DataTable持久化到SQL Server的方法，它是用C#编写的，但是您应该能够相当容易地转换它：

public static string CreateCopyTableDataSQLServer(DataTable dt, string tableName, string connectionString)
{
    //Create the Destination Table based upon the structure of the DataTable
    string sql = string.Empty;
    string retValue = string.Empty;
    StringBuilder sbu;

    try
    {
        if (dt.Rows.Count == 0)
        {
            retValue += "The table " + tableName + " was NOT created because the source table contained zero (0) rows of data";
        }
        else
        {
            sbu = new StringBuilder(string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) DROP TABLE [dbo].[{0}] ", tableName));
            sbu.Append("Create Table " + tableName + " (");

            string dataType = string.Empty;

            foreach (DataColumn column in dt.Columns)
            {
                switch (column.DataType.Name)
                {
                    case "String":
                        dataType = " nvarchar(MAX) ";
                        break;
                    case "DateTime":
                        dataType = " nvarchar(MAX) ";
                        break;
                    case "Boolean":
                        dataType = " nvarchar(MAX) ";
                        break;
                    case "Int32":
                        dataType = " int ";
                        break;
                    case "Byte[]":
                        dataType = " varbinary(8000) ";
                        break;
                    default:
                        dataType = " nvarchar(MAX) ";
                        break;
                }
                string columnName = column.ColumnName.ToString();
                columnName = columnName.FormatProperNameCase();
                columnName = column.ColumnName.ToString().Replace(" ", "_").Replace("-", "_").Replace("#", "_").FormatRemoveNonLettersNumbers();
                sbu.Append("[" + columnName + "]" + dataType + " null, ");
            }

            sbu.Remove(sbu.Length - 2, 2);
            sbu.Append(")");
            sql = sbu.ToString();
            sql = sql.Replace("/", "_").Replace("\\", "_");

            //Copy the Data From the Data Table into the destination Table that was created above
            bool errorRetValue = SQLServerBulkCopy(dt, sql, tableName, connectionString);

            if (!errorRetValue)
            {
                retValue += " \r\n";
                retValue += "There was an error!";
            }
        }
        return retValue;
    }
    catch (Exception ex)
    {
        retValue = string.Format("Error - There was a problem with table {0} and thus it's data has NOT been transferred - {1}", tableName, ex.Message);
        return retValue;
    }
}

public static bool SQLServerBulkCopy(DataTable dt, string Sql, string TableName, string connectionString, bool connectionTypeSQL = true)
{
    try
    {
        if (connectionTypeSQL)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlBulkCopy sqlcpy = new SqlBulkCopy(conn))
                {
                    using (SqlCommand cmd = new SqlCommand(Sql, conn))
                    {
                        cmd.ExecuteNonQuery();
                        sqlcpy.DestinationTableName = TableName;  //copy the datatable to the sql table
                        sqlcpy.WriteToServer(dt);
                    }
                }
            }
            return true;
        }
        else
        {
            throw new ArgumentOutOfRangeException("This method is only for SQL Server Engines");
        }
    }
    catch (Exception ex)
    {
        return false;
    }
}

Answer 2

用于创建新表：

select  *
into    YourDb.dbo.NewTable
from    #YourTempTable

要附加到现有表，请执行以下操作：

insert  YourDb.dbo.ExistingTable
select  *
from    #YourTempTable

Answer 3

在我工作的地方，他们不允许链接服务器，所以我使用VB.NET从不同的服务器传输两个表：

1. 将源Sql表复制到Datatable
2. Copying数据表中的Datatable
3. Mapping列中，复制到目标Sql表

中。

代码如下：

Sub BulkTransferSQLTables(strSchema As String, strTable As String, StrOutputServer As String, StrOutputDatabase As String) ', strEndSrvrDb As String)
    Dim DTBulkTransfer As New DataTable
    'get table of information
    Dim strSchemaTable As String = strSchema & "." & strTable
    Dim sqlstring As String = "Select * from " & strSchemaTable
    Dim Conn As SqlConnection = New SqlConnection("Data Source=" & PubstrServer & ";Initial Catalog=" & PubstrDatabase & ";Integrated Security=True") 'connection to server end
    Dim selectCMD As SqlCommand
    Dim adapter As SqlDataAdapter
    adapter = New SqlDataAdapter(sqlstring, Conn)


    'fill dataset
    Conn.Open()
    adapter.Fill(DTBulkTransfer)

    'Debug.Print(DTBulkTransfer.Rows.Count & " Rows, " & DTBulkTransfer.Columns.Count & " Cols ") 'works


    'build create table statement using details of destination table
    Dim strColname As String
    Dim intRecCount As Integer
    'Dim strSchema As String = "SuffolkPseudo"
    'Dim strTable As String = "Acute_Supporting"
    strSchemaTable = strSchema & "." & strTable
    Dim strCreateTableSQL As String = "CREATE TABLE [" & StrOutputDatabase & "].[" & strSchema & "].[" & strTable & "]("

    Dim strSQL As String = " select [Statement], [RowNo] = ROW_NUMBER() OVER (ORDER BY Statement) FROM [" & PubstrDatabase & "].[dbo].[vwTableAndColumns] " & _
        "where [TABLE_SCHEMA] = '" & strSchema & "' and table_name = '" & strTable & "'"

    Dim strSQL2 As String = "" & _
    " with CTE as ( " & _
    " " & _
    strSQL & _
    " ) " & _
    " " & _
    " select count(*) from CTE "

    intRecCount = GetSQLTableVal(strSQL2)

    For X = 1 To intRecCount
        strSQL2 = "" & _
                " with CTE as ( " & _
                " " & _
                strSQL & _
                " ) " & _
                " " & _
                " select * from CTE "
        strColname = GetSQLTableVal(strSQL2 & " Where [RowNo] = " & X)

        strCreateTableSQL = strCreateTableSQL & " " & Chr(13) & strColname
    Next

    strCreateTableSQL = Microsoft.VisualBasic.Left(strCreateTableSQL, Microsoft.VisualBasic.Len(strCreateTableSQL) - 1) & ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]"

    Debug.Print(strCreateTableSQL)


    Conn.Close()

    Dim Conn2 As SqlConnection = New SqlConnection("Data Source=" & StrOutputServer & ";Initial Catalog=" & StrOutputDatabase & ";Integrated Security=True") 'connection to server end


    'create sql string to check if table exists or not.

    strSQL = " select count(*) FROM [" & StrOutputDatabase & "].sys.Tables as t1 " & _
            " inner join [" & StrOutputDatabase & "].sys.schemas as t2 " & _
            " ON t1.schema_id = t2.schema_id" & _
            " where t2.name = '" & strSchema & "' " & _
            " and t1.name = '" & strTable & "'"


    If GetSQLTableValExternalServer(strSQL, StrOutputServer, StrOutputDatabase) > 0 Then

        Conn2.Open()
        'drop old table in destination area and recreate table.
        strSQL = "drop table " & StrOutputDatabase & "." & strSchema & "." & strTable
        selectCMD = New SqlCommand(strSQL, Conn2)
        selectCMD.CommandTimeout = 600
        selectCMD.ExecuteNonQuery()
        Conn2.Close()
    End If

    Conn2.Open()
    'create the table structure 
    selectCMD = New SqlCommand(strCreateTableSQL, Conn2)
    selectCMD.CommandTimeout = 600

    selectCMD.ExecuteNonQuery()


    'list datatable columns 

    Dim name(DTBulkTransfer.Columns.Count) As String
    Dim i As Integer = 0

    'transfer to sql database from datatable to newly created empty detsination table
    Using bulkcopy As SqlBulkCopy = New SqlBulkCopy(Conn2)

        bulkcopy.BulkCopyTimeout = 3000
        bulkcopy.DestinationTableName = strSchemaTable


        For Each column As DataColumn In DTBulkTransfer.Columns
            name(i) = column.ColumnName
            Dim ColMap As New SqlBulkCopyColumnMapping(name(i).ToString, name(i).ToString)
            bulkcopy.ColumnMappings.Add(ColMap)
            Debug.Print("dt COLUMN: " & name(i).ToString)
            i += 1
        Next


        bulkcopy.WriteToServer(DTBulkTransfer)
    End Using

    Conn2.Close()

    MsgBox("Bulk Transfer Complete")


End Sub

谢谢

Eddy Jawed