Bulk Insert Sql Server millions of record

c# sql-server windows-services bulkinsert
Arief picture Arief · May 24, 2012 · Viewed 22.9k times · Source

I have a Windows Service application that receives a stream of data with the following format

IDX|20120512|075659|00000002|3|AALI                 |Astra Agro Lestari Tbk.                                     |0|ORDI_PREOPEN|12  |00000001550.00|00000001291.67|00001574745000|00001574745000|00500|XDS1BXO1|                                        |00001574745000|ݤ
IDX|20120512|075659|00000022|3|ALMI                 |Alumindo Light Metal Industry Tbk.                          |0|ORDI        |33  |00000001300.00|00000001300.00|00000308000000|00000308000000|00500|--U3---2|                                        |00000308000000|õÄ

This data comes in millions of rows and in sequence 00000002....00198562 and I have to parse and insert them according to the sequence into a database table.

My question is, what is the best way (the most effective) to insert these data into my database? I have tried to use a simple method as to open a SqlConnection object then generate a string of SQL insert script and then execute the script using SqlCommand object, however this method is taking too long.

I read that I can use Sql BULK INSERT but it has to read from a textfile, is it possible for this scenario to use BULK INSERT? (I have never used it before).

Thank you

update: I'm aware of SqlBulkCopy but it requires me to have DataTable first, is this good for performance? If possible I want to insert directly from my data source to SQL Server without having to use in memory DataTable.

Answer

Mark Byers picture Mark Byers · May 24, 2012

If you are writing this in C# you might want to look at the SqlBulkCopy class.

Lets you efficiently bulk load a SQL Server table with data from another source.

Related questions

How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More
How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More
Call a stored procedure with parameter in c#

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database. This a button insert I make work well. private void btnAdd_Click(object …

Read More