Bulk insert update

Question

Hi All.

This is my first post in this forum.I am in a big problem.hope you guys can help me. I have a very large csv file like 1.71gb containing 3.5 crore records.i need to read the data & do some manipulaton & insert the records in the data base.now it is taking a very long time for the exe to reasd the file.it is only reading 12000 lines apprx in 90 mins.i need to read the data as soon as possible.i have created 8 instances & splitted the file in to smaller files..there are a total of 400 files with 85000 lines each..i have to use some joins to get other fields before inserting & aslo need to check if a particular name exists & if yes i need to updtae some field of the records.the fieds to be updated is passed dynamically..

Can any one help me.

Thanks

vishal sahani · Answer

this is my vb code for the exe i have created Imports System.Data Imports System.Data.SqlClient Imports System.IO Imports System.Data.OleDb Public Class Form1 Public conStr As String =  Public connString As String Public strNewPath As String 'Public Extension As String Public ackdatemonth As String Public ackdateday As String Public FilePath ticketno device ackdate port vehicle_no transporter_no vehicle_id finalmonth zone finalzone location_code As String Dim sqlcon As SqlConnection Dim sqlqry sql insertqry updateqry zoneqry As String Dim dsObj dsobj1 dsobj2 As DataSet Dim daObj As SqlDataAdapter Dim dt As DataTable Dim dr As SqlDataReader Dim cmd As SqlCommand Dim count As Integer Dim files() As String Private Sub Form1_Load(ByVal sender As System.Object ByVal e As System.EventArgs) Handles MyBase.Load Timer1.Start() End Sub Private Shared Function InlineAssignHelper(Of T)(ByRef target As T ByVal value As T) As T target = value Return value End Function Private Shared Function getMonth(ByVal monthabb As String) Try Select Case monthabb Case 01 monthabb = Jan Case 02 monthabb = Feb Case 03 monthabb = Mar Case 04 monthabb = Apr Case 05 monthabb = May Case 06 monthabb = June Case 07 monthabb = July Case 08 monthabb = Aug Case 09 monthabb = Sept Case 10 monthabb = Oct Case 11 monthabb = Nov Case 12 monthabb = Dec End Select Return monthabb Catch ex As Exception WriteLine(ex.Message) End Try End Function Private Sub Timer1_Tick(ByVal sender As System.Object ByVal e As System.EventArgs) Handles Timer1.Tick conStr = ..... sqlcon = New SqlConnection(conStr) 'get all the files in the array files = Directory.GetFiles(pathe where file is located *.csv) For i = 211 To 250 FilePath = files(i) Dim fs As New FileStream(FilePath FileMode.OpenOrCreate FileAccess.ReadWrite FileShare.None) Dim sr As New StreamReader(fs) Dim read As String = Nothing sr.ReadLine() While (InlineAssignHelper(read sr.ReadLine())) IsNot Nothing Try 'getting the records by each line and splittng to get the fields If IsDBNull(read) = False And read.ToString().Trim()  Then ticketno =  ticketno = read.ToString().Trim() Dim ary() As String Dim ackdateary() As String ary = ticketno.Split() If ary.Length = 6 Then device = ary(0) ackdate = ary(4) ackdateary = ackdate.Split(-) ackdatemonth = ackdateary(1) ackdateday = ackdateary(2).Substring(0 2) port = ary(5) 'depending upon the above fields need to get more fields 'from database sqlqry = select fd.vchvehicle_nods.numtransporter_nods.intvehicle_idfm_latestrec$.zonefd1.location_code from devicesettings$ ds INNER JOIN fmsvehicle_det$ fd on ds.intvehicle_id = fd.intvehicle_id inner join fm_latestrec$ on fm_latestrec$.intvehicle_id=ds.intvehicle_id inner join fm_location_det fd1 on ds.numtransporter_no = fd1.location_id WHERE ds.DeviceNumber = ' & device & ' daObj = New SqlDataAdapter(sqlqry sqlcon) dt = New DataTable daObj.Fill(dt) If dt.Rows.Count > 0 Then vehicle_no = dt.Rows(0).Item(vchvehicle_no).ToString() transporter_no = dt.Rows(0).Item(numtransporter_no).ToString() vehicle_id = dt.Rows(0).Item(intvehicle_id).ToString() zone = dt.Rows(0).Item(zone).ToString() location_code = dt.Rows(0).Item(location_code).ToString() End If 'need to change the monthe to abbreviated form finalmonth = getMonth(ackdatemonth) 'need to get the required filed to check for duplication sql = Select * from count_analysisnews where vehicle_no = ' & vehicle_no & ' and dtmonth = ' & finalmonth & ' cmd = New SqlCommand(sql sqlcon) If sqlcon.State = ConnectionState.Closed Then sqlcon.Open() dr = cmd.ExecuteReader() If dr.Read() Then insertqry = update lcount_analysisnews set [ & ackdateday & ] = 'Y' where vehicle_no = ' & vehicle_no & ' and dtmonth = ' & finalmonth & ' Else insertqry = Insert into count_analysisnews (vehicle_no dtmonth location_id intvehicle_id[ & ackdateday & ]) values (' & vehicle_no & '' & finalmonth & '' & transporter_no & ' ' & vehicle_id & ''Y')  End If dr.Close() cmd = New SqlCommand(insertqry sqlcon) cmd.ExecuteNonQuery() If sqlcon.State = ConnectionState.Open Then sqlcon.Close() End If End If Catch ex As Exception Timer1.Start() End Try End While sr.Close() fs.Close() Next i Me.Dispose() End Sub End Class

Vilas Gite · Answer

hi vishal... .net provides class SQLBULKCOPY in System.Data.SqlClient namespace... for more details and example how to done it...just check below link. http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server ----------------------- If this reply hepls your post…then  MARK AS ANSWER !

Suthish Nair · Answer

Hi Vishal Welcome to Forums.. Normally for these type of cases avoid doing logical operations on the data in code behind itself. I mostly dumps junk data files into an SQL temp table later at back end using a stored procedure will do all the logical kind of operations ( do some manipulaton & insert the records ). I think this only best alternative way to do.

Prasant Jinaga · Answer

Hi Vishal There is a class called SqlBulkCopy in .Net Framework which is been used insert the bulk data into sql.You can try with that class.It will take less time rather than its taking now. H@ppy Coding!!! Thanks Prasant

Jiteendra Sampathirao · Answer

Hi Solution is Normalization...