question

ramaratna avatar image
ramaratna asked

Bulk insert into sql server

Please help how to bulk insert into db

sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

If you are planning on loading from a .NET app, then you want to make use of the SqlBulkCopy class, which allows you to specify mappings and fill data from any source that you like.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered

That's a big question. Have you looked it up in Books Online? You're more likely to get help if you try it and come back with a more specific question.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image
Ian Roke answered

BULK INSERT in SQL Server is as you would expect a very powerful thing.

For example you could start off with something very simple that just splits by using a pipe character '|' as a field terminator and \n as the row terminator like so...

BULK INSERT SomeTable
FROM 'c:\somedata.txt'
WITH 
(
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n'
)

More complicated examples would be Using a Format File to Bulk Import Data, Using a Format File to Skip a Table Column and Using a Format File to Map Table Columns to Data-File Fields .

The best thing to do, although it is probably painful to hear me say it, is read, read, read the documentation.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ramaratna avatar image
ramaratna answered

ok, i am using a function for bluck insert, it works prefectly fine but only if there are no FK for the tables. i posted the question expecting for a diffrent solution which can give an idea or a hint . and i am glad that i always get the excepted ans from Matt Whitefield.

i am posting the function which i am using in my application.

Public Sub importExcelData(ByVal tableName As [String], ByVal execlData As DataTable, ByVal transid As Integer, ByVal sourceoption As String)

    Dim ds As New DataSet
    Dim arrTemp As New ListItemCollection()
    Dim strsql As String = ""
    Dim sqlcmd As New SqlClient.SqlCommand
    Dim j As Integer
    Dim count As Integer
    Dim strdcol As String = ""
    Dim destinationfields As String = ""
    Dim sourcefields As String = ""
    Dim sSqlConnectionString As String = ConfigurationManager.AppSettings("MyConnectionString").ToString
    Dim sqlconn As SqlConnection = New SqlConnection(sSqlConnectionString)
    sqlconn.Open()

           ds = CLlogic.getcolumnnames("Test")

    ''''TRANSACTIONID AND SOURCE COLUMN NEEDS TO ADD AND HAVE TO ASSIGN THE VALUE

    For i = 0 To ds.Tables(0).Rows.Count - 1
        For j = 0 To execlData.Columns.Count - 1
            If ds.Tables(0).Rows(i).Item("name").ToString.ToLower = execlData.Columns(j).ColumnName.ToLower Then
                count = count + 1
            End If
        Next
        Dim transcolumn As DataColumn
        transcolumn = New DataColumn(ds.Tables(0).Rows(i).Item("name"))
        If count = 0 Then
            If ds.Tables(0).Rows(i).Item("name") = "Source" Then
                transcolumn.DataType = System.Type.GetType("System.String")
                transcolumn.DefaultValue = sourceoption
                execlData.Columns.Add(transcolumn)
            Else
                If ds.Tables(0).Rows(i).Item("name") = "Transactionsid" Then
                    transcolumn.DataType = System.Type.GetType("System.Int32")
                    transcolumn.DefaultValue = transid
                    execlData.Columns.Add(transcolumn)
                Else
                    If ds.Tables(0).Rows(i).Item("name") <> "Id" Then
                        transcolumn.DataType = System.Type.GetType("System.String")
                        execlData.Columns.Add(transcolumn)

                    End If

                    End If
            End If
            count = 0
        End If
            count = 0
    Next


    For j = 0 To ds.Tables(0).Rows.Count - 1
        If destinationfields = "" Then
            destinationfields = ds.Tables(0).Rows(j).Item("name")
        Else
            destinationfields = destinationfields + ";" + ds.Tables(0).Rows(j).Item("name")
        End If
    Next


    For j = 0 To execlData.Columns.Count - 1
        If sourcefields = "" Then
            sourcefields = execlData.Columns(j).ColumnName.ToString
        Else
            sourcefields = sourcefields + ";" + execlData.Columns(j).ColumnName.ToString
        End If
    Next

    Dim sbc As New SqlBulkCopy(sqlconn)
    Dim arrSrc As String() = sourcefields.Split(New Char() {";"c}, StringSplitOptions.RemoveEmptyEntries)
    Dim arrDst As String() = destinationfields.Split(New Char() {";"c}, StringSplitOptions.RemoveEmptyEntries)

    'MAPPING DATACOPY COLUMNS 

    ' srcColumn has to be the same with source table column names 
    'dstColumn has to be the same with destinate table column names 
    'It's case sensitive 

    Dim srcColumn As String = String.Empty
    Dim dstColumn As String = String.Empty
    For index As Integer = 0 To arrSrc.Length - 1
        srcColumn = arrSrc(index).Trim()
        'dstColumn = arrDst(index).Trim()
        sbc.ColumnMappings.Add(srcColumn, srcColumn)
    Next
    Dim destinationTableName As String = ""
    destinationTableName = "Test"
    sbc.DestinationTableName = destinationTableName
    sbc.WriteToServer(execlData)
    sbc.Close()

End Sub
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.