Fast batch insert/update with SQL Server 2008 and BCP

I'm not a good SQL programmer, I've got only the basics, but I've heard of some BCP thing for fast data loading. I've searched the internet and it seems to be a command-line only utility, and not something you can use in code.

The thing is, I want to be able to make very fast inserts and updates in a SQL Server 2008 database. I would like to have a function in the database that would accept:

• The name of the table I want to execute an insert/update operation against
• The names of the columns I'll be feeding data to
• The data in a CSV format or something that SQL can read stupid-fast
• A flag indicating weather the function should perform an insert or update operation

This function would then read this CSV string and genarate the necessary code for inserting/updating the table.

I would then write code in C# to call that function passing it the table name, column names, a list of objects serialized as a CSV string and the insert/update flag.

As you can see, this is intended to be both fast and generic, suitable for any project dealing with large amounts of data, and thus a candidate to my company's framework.

Am I thinking right? Is this a good idea? Can I use that BCP thing, and is it suitable to every case?

As you can see, I need some directions on this... thanks in advance for any help!

Answers

In C#, look at SQLBulkCopy. It's what SSIS uses in the background.

For true bcp/BULK INSERT, you'd need bulkadmin rights which may not be allowed

Have you considered using SQL Server Integrated Services (SSIS). It's designed to do exactly what you describe. It is very fast. You can insert data on a transactional basis. And you can set it up to run on a schedule. And much more.

Need Your Help