Bulk insert questions

Question

I have a client side CSV file and I want to develop a C # application for mass data entry into a database table with minimal log output. I am confused if I use ADO.NET on the client side to call stored procedures on the database server. What code needs to be developed on the client side and what code needs to be implemented on the server side as stored procedures?

But I did not find any samples from Google. What are ready-to-use samples ?:-)

EDIT: Additional Information:

I have a lot of data on the client side and I want to import it into the database, but I do not need the overhead in all transaction logs. For security reasons, I want to create a stored procedure on the server side and make a call on the client side (ADO.NET). I want to know in order to achieve such a goal. What type of T-SQL needs to be developed in stored procedures on the server side and how to efficiently make calls / populate data on the client side?

If something else is unclear, please let me know.

Answer 1

You can bind CsvReader to SqlBulkCopy, which does the job very nicely ... something like (untested):

using (CsvReader reader = new CsvReader(path)) using (SqlBulkCopy bcp = new SqlBulkCopy(CONNECTION_STRING)) { bcp.DestinationTableName = "SomeTable"; bcp.WriteToServer(reader); } 

change , you usually do a bulk insert into the intermediate table, and then use a regular stored procedure to move the data to the real table.

Answer 2

Are you using SQL Server 2008? And can you execute dynamic SQL (not that I protected it)?

If so, you can build an insert statement that uses "String Console". Essentially, the insert statement will now take an array of arguments for each row, for example:

 INSERT INTO TableA (Col1, Col2) VALUES ('A', 'B'), ('C', 'D') 

Read more about this in the blog post, " SQL Server 2008 - Insert multiple records using a single insert statement - Using the row constructor ."

Hope this helps.