3

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.

Improve this question

5 Answers 5

Reset to default
5

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Agreed (+1). This can use either DataTable or IDataReader, and is by far the best way to bulk insert from .NET into SQL Server.
2

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

Improve this answer

Comments

0

You can try SSIS for this purpose

Improve this answer

Comments

0

You can INSERT a SELECT like this:

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

Hope it helps.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.