Geek Answers Handbook

The fastest way to get data from an array into a SQLServer database?

Problem: How to most efficiently move data from an array to a SQL Server table.

Details I created an array with many rows (usually about 100,000) and many columns (about 40) in memory in a WinForms application. I need to get this array into the appropriate SQL Server table in the fastest way. Right now I am creating a SqlCommand object, iterating over 100,000 rows in my array and assigning 40 parameters to the command object for each row, and then calling ExecuteCommand. It works, but slow and certainly should not be the most efficient way to do this. Should I put all the data from the array into a DataTable and then somehow send the data table right away (I don't know how to do this)? Or some other method? Write to a file and use bcp (it seems that it will not be faster, I have not tried). Any suggestions appreciated!

+3
source share
4 answers

SqlBulkCopy . It would be even better if you could store the material in memory like DataTablethat because one of the method overloads WriteToServer()takes one.

EDIT: Here is an example on how to use the API.

+5
source

We use xml for large data packages. Pass the xml string to the stored procedure and ask sp to split the xml into a table. Then select into your database from the temp table. We seem to work very well for us.

Edit: As noted in the comments below, the process that converts xml to a table sp_xml_preparedocument.

declare @idoc int
exec sp_xml_preparedocument @idoc output, @input_xml
-- select into a tmp table from openxml(@idoc, 'xpath_to_your_data')
exec sp_xml_removedocument @idoc
+1
source

Pinal Dave , :

INSERT INTO MyTable  (FirstCol, SecondCol)
    SELECT  'First' ,1
    UNION ALL
SELECT  'Second' ,2
    UNION ALL
SELECT  'Third' ,3
...

, , .

+1
source
  • Display data in data format in a winforms application.
  • Copy data to Excel (or OpenOffice Calc).
  • Save file.
  • Use the Import Data tool to retrieve data and map fields.

You would do it in 15 minutes.

Edit: ugh, this might not work for 100k lines. But it’s quick, easy and worth it to move on to something more complex.

0
source

More articles:


All Articles