CODEWITHSUNDEEP
c#asp.netsql-serverbulkinsert
dgxshiny
dgxshiny

Reputation: 107

Bulk insert, asp.net

I have a need to take in a list of ID numbers corresponding to a member. Their can be anywhere from 10 to 10,000 being processed at any given time. I have no problem collecting the data, parsing the data and loading it in to a DataTable or anything (C#) but I want to do some operations in the database. What is the best way to insert all of this data into a table?

I am pretty sure I don't want run a for each statement and insert 10,000 times.

Upvotes: 3

Views: 2831

Answers (5)

Remus Rusanu
Remus Rusanu

Reputation: 294407

With SQL Server 2008 you can pass the C# data set (the actual DataTable object) as a table valued parameter. The parameter is available in your Transact-SQL code as an @table variable you can query and manipulate.

Upvotes: 0

Havenard
Havenard

Reputation: 27914

You can INSERT a SELECT like this:

INSERT INTO myTable (a, b, c) SELECT d, e, f FROM anotherTable WHERE ...

Hope it helps.

Upvotes: 0

Sorantis
Sorantis

Reputation: 14722

You can try SSIS for this purpose

Upvotes: 0

Shea Daniels
Shea Daniels

Reputation: 3270

I've used the SqlBulkCopy class before to do a couple million adds. It seemed pretty handy.

Upvotes: 5

JMP
JMP

Reputation: 7844

You may not want to execute an INSERT 10,000 times, but you can wrap those 10,000 inserts into 1 transaction.

Upvotes: 2

Related Questions