Batch insert/update using stored procedure

Can anyone give me a sample script for batch insert/updat开发者_JAVA百科e of records in table using stored procedure in SQL Server?

I've done something like this in the past:

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 applies for multi-column tables as well. You then do this:

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

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

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;

These were taken from here which also looks at performance of this and the xml approach on the SQL query end.

This looks at performance on the data transmission end. Keep both in mind and the size of the data you are passing back and forth and how it is going to be used in a query to choose the best approach.