Batch insert / update using stored procedure

Question

Can someone give me a sample script to batch insert / update records in a table using a stored procedure in SQL Server?

Answer 1

In the past, I have done something like this:

CREATE PROCEDURE InsertProductIds(@ProductIds xml) AS INSERT INTO Product (ID) SELECT ParamValues.ID.value('.', 'VARCHAR(20)') FROM @ProductIds.nodes('/Products/id') as ParamValues(ID) END 

Obviously, this is just a single-column table, but the XML approach also applies to multi-column tables. Then you do the following:

 EXEC InsertProductIds @ProductIds='<Products><id>3</id><id>6</id></Products>' 

Answer 2

Sending a table value parameter is another option.

SQL

 CREATE TYPE TestTableType AS TABLE ( ID INT, Name NVARCHAR(100), Description NVARCHAR(2000) ); GO CREATE proc [dbo].[Test_Table_Parameter] @Tbl TestTableType READONLY as SELECT 'Return' GO 

the code

 var param = new SqlParameter(); param.ParameterName = "@Tbl"; param.SqlDbType = SqlDbType.Structured; var dt = new DataTable(); var str = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" + DateTime.Now; //map the fields to datatypes here dt.Columns.Add("ID", typeof (Int32)); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Description", typeof(string)); for (var i = 0; i < rows; i++) { dt.Rows.Add(new object[] {i + 1, (i + 1).ToString(), str }); } param.Value = dt; 

They were taken from here , which also discusses the performance of this and the xml approach at the end of the SQL query.

This discusses end-to-end performance. Keep in mind the size of the data that you pass back and forth and how it will be used in the request to choose the best approach.