Bulk Insert of data from multiple text files to MS SQL database

[poojavb]

Hello,

Can we perform bulk insert for SQL data?

I have the complete SQL data in text files. There are multiple tables and the corresponding text files.

I tried to do the bulk insert but always got some new error.

Please help me to get an answer.

My code

Imports System.IO
Imports System.Data.SqlClient
Public Class Form2

     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
          Dim FileName As String
          Dim dbstatus As Boolean
          Dim sr As StreamReader
          Dim line As String
          Dim linesCount As Integer
          Dim lines As String()
          Dim i, j As Integer
          Dim Names_Of_Tables() As String

          Dim myconn As SqlConnection = New SqlConnection
          dbstatus = Open_NDB_Connection(myconn)

          Dim dbCmd As SqlCommand = New SqlCommand()

          Names_Of_Tables = {"Local_IP_Config", "Login_Informations","Remote_IP_Config", "Restricted"}

          For i = 0 To Names_Of_Tables.Length - 1
               FileName = "D:\DATA\" & Names_Of_Tables(i) & ".txt"
               If File.Exists(FileName) Then
                    Debug.Print("Filename: " + FileName)
                    Debug.Print("Names_Of_Tables: " + Names_Of_Tables(i))
                    'If FileName = Names_Of_Tables(i) Then
                    sr = New StreamReader(FileName)
                    lines = IO.File.ReadAllLines(FileName)

                    linesCount = Integer.Parse(lines.Length)
                    Debug.Print(FileName + " lines = " + linesCount.ToString)
                    line = sr.ReadLine()
                    Try
                         For j = 0 To linesCount - 1
                              dbCmd = New SqlCommand("BULK INSERT " & Names_Of_Tables(i) & " FROM '" & FileName & "' WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')", myconn)
                              Debug.Print("Query: " + dbCmd.CommandText)
                              dbCmd.ExecuteNonQuery()
                              line = sr.ReadLine()
                         Next
                    Catch ex As Exception
                         MsgBox(ex.Message)
                    End Try

                    'End If
               Else
                    MsgBox("File " + FileName + " does not exists")
               End If
          Next
     End Sub
End Class

'Connection code is as follows

Imports System.Data.SqlClient

Module Module1
     Public Function Open_NDB_Connection(ByRef conn As SqlConnection) As Boolean
          Try
               conn.ConnectionString = "Data Source = LocalHost\;Initial Catalog = MY_DB;Integrated Security  = True"
               conn.Open()
          Catch ex As Exception
               MsgBox(ex.Message)
          End Try
          Return True
     End Function

     Public Function Close_NDB_Connection(ByRef conn As SqlConnection) As Boolean
          Try
               conn.Close()
               Return True
          Catch ex As Exception
               MsgBox(ex.Message)
               Return False
               Exit Function
          End Try
     End Function
End Module

[Reverend Jim]

It would help to know the specific error.

Perhaps a simple example that I have tested locally. I have a table named "users" defined as

    UserID      int
    UserName    varchar(50)
    UserType    varchar(50)

and a text file (bulk.txt) that contains (tab delimited)

    1    Jim     user
    2    George  admin
    3    Fred    admin
    4    Amy     user
    5    Ellen   user
    8    Joyce   user

To insert all of the records at once (assuming no duplicate key conflicts) I would execute the query

    BULK INSERT users FROM 'D:\temp\bulk.txt' WITH (FIELDTERMINATOR = '\t')

and the query would be created in code as

    qry = "BULK INSERT users                " & _
          "     FROM 'D:\temp\bulk.txt'     " & _
          "     WITH (FIELDTERMINATOR = '\t')"

One "gotcha" is that you must be a member of the sysadmin or bulkinsert group to execute a BULK INSERT.

[poojavb]

the query works for a single table and not multiple tables.....it was showing some data type error....

anyways i got it working...
First I took the exact values that were there in the tables to text files and then used the same above code...and it worked...