How to send huge data in sql faster

[Pallavivalunj]

Hii,

I m working with excel which has data around 80k to 1L per sheet.
While taking the data in collection i get error of memory exception.
What i wanna to do is to send the excel sheet data to sql table(tables are already created). And the call procedures which are also done.
How can i transfer the data direct from excel to sql is there any option.
Or else how can i come over the situation im facing right now.

Please help me to get solution.

Thanka in advance...

 

[sivagelli]

Using T-SQL OPENROWSET, you can perform the data move from excel to SQL server. Check this link for more details-
https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-2017

 

[naveedraza]

Use SQLBULKCopy function. i attached Code for your refernce

 

[sivagelli]

Use SQLBULKCopy function. i attached Code for your refernce

@naveedraza I am supposing the code writes collection data to sql server, isnt it?

 

[naveedraza]

Yes, it will append the collection data to sql server

 

[sivagelli]

@naveedraza Reading the problem statement, @Pallavivalunj is looking to insert data directly in to SQL Server from Excel as there is memory issue while copying such huge data of 80k to 1lakh records from Excel into Collection.
I am assuming, your solution holds well when trying to write collection data to SQL Server but not when writing data from Excel file to SQL Server. Let me know if you differ!

 

[naveedraza]

Yes, here you have to use odbc connection to load data from Excel to collection. I will share you that code too today evening

 

[sivagelli]

Sure, that would help the community!

The suggestion i shared using OPENROWSET is a single step to import data from excel file to SQL Server, however would need configurations. Refer to the link.

 

[Pallavivalunj]

@sivagelli @naveedraza Thanks for Your Reply.
I have used vb.net code for this issue im sharing the same for ur reference.


Code:

moConnection = New OLEDBConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& Excel_File_Path &";Extended Properties=""Excel 12.0 Xml;HDR=YES"";")

moConnection.Open()

Dim strCommand

Dim oDataAdapter As New OLEDBDataAdapter(ExcelQuerry,moConnection)



Dim oDataSet As New DataSet

oDataAdapter.Fill(oDataset)



Results = oDataSet.Tables(0)



If SQLConnection IsNot Nothing Then Return

Try

SQLConnection = New SqlConnection(SQLConnectionString)

SQLConnection.Open()

strCommand = "TRUNCATE TABLE BRCiti_Statement"

Dim command1 As SqlCommand = New SqlCommand(strCommand, SQLConnection)

command1.CommandType = CommandType.Text

command1.ExecuteNonQuery()



For Each Drr As DataRow In oDataset.Tables(0).Rows

strCommand = "INSERT INTO BRCiti_Statement (AccountNo,Date,Amount,Debit_CreditMarker,Ref1,Ref2,Description,Details,CustAccountNo) VALUES ('" & Drr(0).ToString & "','" & Drr(1).ToString & "','" & Drr(2).ToString & "','" & Drr(3).ToString & "','" & Drr(4).ToString & "','" & Drr(5).ToString & "','" & Drr(6).ToString & "','" & Drr(7).ToString & "','" & Drr(8).ToString & "')"

Dim command As SqlCommand = New SqlCommand(strCommand, SQLConnection)

command.CommandType = CommandType.Text



If (command.ExecuteNonQuery().Equals(1)) Then

Message = "Information stored in database"

Else

Message = "Not stored in database"

Exit For

End If



Next

moConnection.Close()

SQLConnection.Close()

Catch ex As Exception

Success = False

Message = ex.Message



End Try
INPUT: Table, SQL Connection String, Excel Query, Excel File Path.
Output: Result(Collection) ,message,success,Record Affected


Try this and let me know

 

[naveedraza]

Yes, on monday i will share my code.... Actually i forget to send you the code

 

[AnandManokaran]

Yes, on monday i will share my code.... Actually i forget to send you the code

Can you please share the code ??

 

[naveedraza]

Hi, Sorry again,

here is the code:

i wrote a global function, how you can run this code is

1. in object -> In Initialise Tab -> Open the Rectangular box -> In Global code Tab write the attached screenshot code
2. add new page -> in code stage -> write this code : DataTable dtb = ExcelRead(FullFileNamePath)

 

[Pallavivalunj]

@naveedraza Thank u for this code.

 

[princesatya]

Hi

Hi, Sorry again,

here is the code:

i wrote a global function, how you can run this code is

1. in object -> In Initialise Tab -> Open the Rectangular box -> In Global code Tab write the attached screenshot code
2. add new page -> in code stage -> write this code : DataTable dtb = ExcelRead(FullFileNamePath)[/QUOT


Hi

Hi, Sorry again,

here is the code:

i wrote a global function, how you can run this code is

1. in object -> In Initialise Tab -> Open the Rectangular box -> In Global code Tab write the attached screenshot code
2. add new page -> in code stage -> write this code : DataTable dtb = ExcelRead(FullFileNamePath)

Hi,
,
Can you please share the input and output value, what i need to place it. Can you share in detail

 

[princesatya]

Hi, Sorry again,

here is the code:

i wrote a global function, how you can run this code is

1. in object -> In Initialise Tab -> Open the Rectangular box -> In Global code Tab write the attached screenshot code
2. add new page -> in code stage -> write this code : DataTable dtb = ExcelRead(FullFileNamePath)

It getting error like dll are missing. Could you please tell me know which dll we need to use it

 

[hemanth kumar]

Can someone please help me in writing code for bulk insert of BP collection data(5000Rx30C) into MS Access table in a single go using code stage...
Any assistance is highly appreciated.