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
