使用存储过程批量插入/更新

Can anyone give me a sample script for batch insert/update of records in table using stored procedure in SQL Server?

任何人都可以使用SQL Server中的存储过程为我提供一个示例脚本，用于批量插入/更新表中的记录吗？

2 个解决方案

#1

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:

显然，这只是一个单列表，但XML方法也适用于多列表。然后你这样做：

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

#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

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.

这些都是从这里获取的，这也是在SQL查询结束时查看这个和xml方法的性能。

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.

这关注数据传输端的性能。记住要记住的数据和来回传递的数据的大小，以及如何在查询中使用它来选择最佳方法。

#1