Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

Best Way to Insert Data

I'm wondering what's currently best practice for inserting a lot rows into a DB table using ADO.NET.  Let's say I have to insert 1000 rows from a client program into a DB table.  I know the rows do not exist in the table, so I just want to append them to the table and be done with it.

Some solutions I've come up with:

1.  Use a DataTable and the associated ADO.NET machinery to do the Insert.  This, I believe, calls a Command object once for each row.

2.  Write the rows into a string builder as a sequence of insert operations.  Execute the resulting string as a batch on the server.

3.  Perhaps write the data into a file and use either the bulk copy program or a DTS program to pull the data into the DB

4.  Try to use Diffgrams??

Anyone know which approach works best?  Thanks in advance for any input.

Wednesday, August 18, 2004

Do the simplest one. Just create a SqlCommand with the relevant Parameters and have a loop and just change the values and invoke the command for each row. Don't worry about the round-trips - it's incredibly fast.

The problem with 2) is that there is a limit (albeit pretty large) to the size of command you pass to SQL Server.

Duncan Smart
Thursday, August 19, 2004

Thanks, Duncan.  That's pretty much the decision I reached, as well.  If I eventually run into a problem with the approach, I'll look at the alternatives at that time.

Thursday, August 19, 2004

*  Recent Topics

*  Fog Creek Home