SQL Server中的批量插入

Question

我创建了一个SSIS包，从csv文件中导入数据到我的表中。现在我想通过SQL命令来复制这个SSIS包，具体来说是使用bulk insert语句。你能帮忙吗？

csv文件的属性如下图所示：

- dmitry

Answer 1

你可以使用 Bulk Insert 来实现这个目的：

BULK INSERT table_name
FROM 'c:\test.csv'
WITH
(
   FIRSTROW= 3,
   FIELDTERMINATOR = ';',
   ROWTERMINATOR = '\n'
)

- Kiril Rusev

---

不幸的是，在我的情况下，这并不起作用。也许需要提供其他属性...不确定... - dmitry

Answer 2

以下内容不使用批量插入命令，而是使用.NET中的SqlBulkCopy类在SSIS脚本任务中进行。

以下是使用SSIS脚本任务将数据从一个表复制到其历史数据库（在另一台服务器上）的脚本任务，使用了批量复制方法。它在SSIS中使用ADO连接。

导入命名空间。

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms

代码

 Public Sub PerformBulkCopy(ByVal tableName As String, ByVal sourceConnection As SqlConnection, ByVal destinationConnection As SqlConnection)
    Dim dtSource As DataTable
    dtSource = New System.Data.DataTable()
    dtSource = GetSourceDataTable(tableName, sourceConnection)


    Using sbc As New SqlBulkCopy(destinationConnection)
        sbc.DestinationTableName = tableName
        sbc.WriteToServer(dtSource)
    End Using
End Sub


Public Function GetSourceDataTable(ByVal tableName As String, ByVal sourceConnection As SqlConnection) As DataTable

    Dim cmd As New SqlCommand("uspGetDataForAchival")
    cmd.Parameters.Add(New SqlParameter("@TableName", tableName))
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Connection = sourceConnection

    Dim dap As New SqlDataAdapter(cmd)
    Dim ds As New DataSet()
    dap.Fill(ds)
    Return ds.Tables(0)

End Function

Public Sub Main()

    Dim tableName As String = Dts.Variables("User::CurrentTable").Value.ToString()
    Dim sourceConnection As SqlConnection
    Dim destinationConnection As SqlConnection

    Try
        ' Retrieve the reference to the managed connection'
        Dim cmSource As ConnectionManager
        cmSource = Dts.Connections("ADOSourceConnection")
        sourceConnection = DirectCast(cmSource.AcquireConnection(Dts.Transaction), System.Data.SqlClient.SqlConnection)

        Dim cmDestination As ConnectionManager
        cmDestination = Dts.Connections("ADOACIConnection")
        destinationConnection = DirectCast(cmDestination.AcquireConnection(Dts.Transaction), System.Data.SqlClient.SqlConnection)

        Dim destinationConnectionString As String = destinationConnection.ConnectionString
        MessageBox.Show(destinationConnectionString)
        PerformBulkCopy(tableName, sourceConnection, destinationConnection)

        'Release Connection'
        cmSource.ReleaseConnection(sourceConnection)
        cmDestination.ReleaseConnection(destinationConnection)

        'Success'
        Dts.TaskResult = Dts.Results.Success


    Catch ex As Exception

        Dim exceptionVariable As Microsoft.SqlServer.Dts.Runtime.Variables = Nothing
        Dts.VariableDispenser.LockOneForWrite("User::ScriptException", exceptionVariable)
        exceptionVariable("User::ScriptException").Value = ex.Message
        exceptionVariable.Unlock()
        Dts.Events.FireError(-1, "Task Name", ex.Message, String.Empty, 0)
        Dts.TaskResult = Dts.Results.Failure
     End Try

    End Sub

- LCJ

Answer 3

你可以使用这段代码。

StreamReader file = new StreamReader(bulk_data_filename);
CsvReader csv = new CsvReader(file, true,',');
SqlBulkCopy copy = new SqlBulkCopy(conn);
copy.DestinationTableName = tablename;
copy.WriteToServer(csv);

- Amarjit Singh Chaudhary