Best method for importing data from text file into MS SQL Server

performance sqlbulkcopy sql-server vb.net

Popular Answer

This offers a technique that might read your text file more quickly.

    Sub readFixWidthTextFileIntoSqlTable()

    Dim sqlConn As New SqlConnection("Connection String Goes Here")
    sqlConn.Open()
    Dim sqlComm As New SqlCommand
    sqlComm.Connection = sqlConn
    sqlComm.CommandType = CommandType.Text
    sqlComm.CommandText = "INSERT INTO YourTableNameHere VALUES(@Field1, @Field2, @Field3, @Field4)"

    sqlComm.Parameters.Add("@Field1", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field2", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field3", SqlDbType.Text)
    sqlComm.Parameters.Add("@Field4", SqlDbType.Text)



    Using IFReader As New FileIO.TextFieldParser(FileNameWithPath)
        IFReader.TextFieldType = FileIO.FieldType.FixedWidth
        IFReader.SetFieldWidths(12, 12, 8, 8)

        While Not IFReader.EndOfData
            Dim fields As String() = IFReader.ReadFields

            sqlComm.Parameters("@Field1").Value = fields(0)
            sqlComm.Parameters("@Field2").Value = fields(1)
            sqlComm.Parameters("@Field3").Value = fields(2)
            sqlComm.Parameters("@Field4").Value = fields(3)
            sqlComm.ExecuteNonQuery()
        End While

    End Using

    sqlConn.Close()
End Sub
1
3/20/2015 5:05:56 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow