有人能给我一个在SQL Server中使用存储过程批量插入/更新表记录的示例脚本吗?
有人能给我一个在SQL Server中使用存储过程批量插入/更新表记录的示例脚本吗?
我过去做过类似的事情:
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
EXEC InsertProductIds @ProductIds='<Products><id>3</id><id>6</id></Products>'
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
代码
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;