Hi,
Via a web service, remote computers will be sending a set of rows to insert into our central sql server.
What is the best way (performance wise) to insert these rows? There could be anywhere from 50-500 rows to insert each time.
I know I can do a bulk insert or format the data as XML that insert it that way, but I've never done this in an enterprise setting before.
Updates using wcf web services (or maybe wse not sure yet) and SQL Server 2008 standard.
What kind of web service is this?
If it's .Net, usually the best way is to load the input into a DataTable, then shoot it up to the SQL server using the SqlBulkCopy class
Unless you're running on a 10 year-old computer, 50-500 rows isn't very many; you could literally send over SQL statements and pipe them directly into the database and get great performance. Assuming you trust the services sending you data, of course :-)
If performance really is an issue sending over a bcp file is absolutely the fastest way to jam data in the database. It sounds from your question that you already know how to do this.
50-500 rows shouldn't be a problem! There is no need to do performance tuning! Do normal (prepared) SQL Statements in your application.
Don't kill it with complexity and overengineering.
When you should insert more than 250.000 rows, you should think about scaling!
Don't turn of the constraints, you might kill the DB.
A mere 50-500 records does not constitute a "bulk insert". The bulk insert mechanism is designed for really massive import of data which is to be immediately followed up with a back up.
In web service I would simply pass the XML into SQL server. The specifics would be version dependent.
To echo all other answers, 500 rows is no issue for SQL server. If you do need to insert a large number of records, the fastest way is with a built-in stored proc called BulkInsert,
which (I Believe) is an entry point to a SQL Server utility designed specifically for doing this called bcp.exe