sql 批量插入、更新。（XML传值）

来源：互联网发布：windows 网络编程编辑：程序博客网时间：2024/06/06 16:24

首先编辑好XML格式，格式为<Root><Row 字段名="值" /><Row 字段名="值" />.....<Root/>，这里我做了两次操作，需求需要。完全一次就可以执行全部。

比如： (因为我的是读取不确定行的Excel数据所以循环条件了肯定为真的 1==1)

strXML.Append("<Root>");

int index = 0;

for (int i = 0; 1 == 1; i++)
                {
                    if (index >= 3)
                    {
                        break;
                    }
                    else
                    {
                        if (clsEx.GetValue(2 + i, 1) == "")
                        {
                            index++;
                        }
                        else
                        {
                            index = 0;
                            strXML.Append("<Row FInComeAccount=\"" + clsEx.GetValue(2 + i, 1) + "\"");
                            strXML.Append(" FClientName=\"" + clsEx.GetValue(2 + i, 2) + "\"");
                            strXML.Append(" FInComeDate=\"" + clsEx.GetDateTimeValue(2 + i, 3) + "\"");
                            strXML.Append(" FInComeAmount=\"" + clsEx.GetValue(2 + i, 4) + "\"");
                            strXML.Append(" FDuring=\"" + clsEx.GetDateTimeValue(2 + i, 5) + "\"");
                            strXML.Append(" FNumber=\"" + clsEx.GetValue(2 + i, 6) + "\"");
                            strXML.Append(" FSJCode=\"" + clsEx.GetValue(2 + i, 7) + "\"");
                            strXML.Append(" FCreateDate=\"" + DateTime.Now + "\"");
                            strXML.Append(" RowIndex=\"" + (2 + i) + "\" />");
                        }
                    }

}

strXML.Append("</Root>");

然后执行一次存储过程，值就是strXML 编辑好的XML格式

sp_Import_PrepareClientAccount ‘“+strXML.ToString()+”’

sql 存储过程：（验证数据合法性）

/****** Object: StoredProcedure [dbo].[sp_Import_PrepareClientAccount]    Script Date: 09/26/2013 10:47:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       hefeng
-- Create date: 2013-9-25
-- Description:   批量添加经销商账目
-- =============================================
ALTER PROCEDURE [dbo].[sp_Import_PrepareClientAccount]
   @XmlStr NVARCHAR(max)
AS
BEGIN

     --DECLARE @XmlStr NVARCHAR(max)
     --SET @XmlStr='<Root>'
     --+'<Row FInComeAccount="HSBC SH" FClientName="宜兴市宜通汽车销售服务有限公司" FInComeDate="7/31/2013 8:00:00 AM" FInComeAmount="32500" FDuring="7/31/2013 8:00:00 AM" FNumber="130731BS40096555" FCreateDate="9/24/2013 5:32:32 PM" RowIndex="16" /> '
     --+'</Root>'

   DECLARE @docHandle int;
   EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlStr;

   SELECT * INTO #temp

     FROM OPENXML(@docHandle, N'Root/Row')
     WITH (FInComeAccount varchar(50),FClientName varchar(200),FInComeDate datetime,FInComeAmount decimal,FDuring varchar(50),FNumber varchar(50),FSJCode varchar(50), FCreateDate datetime,RowIndex int);
     EXEC sp_xml_removedocument @docHandle;

     --select * from #temp

    CREATE TABLE #ErrorList
   (
       StrMessage varchar(200)
   )

   ---------查询,如果经销商名称及编号存在,则合法,否则添加到错误表
   DECLARE @row varchar(5000);

   select @row=ISNULL(@row+',','')+convert(varchar(50), #temp.RowIndex) from t_Dic_Customlist_RelationClient rc
   inner join t_Dic_Customlist_RelationClientDetail rcd on rc.ID = rcd.FID
   inner join #temp on rc.名称 = #temp.FClientName and rcd.FSJCode = #temp.FSJCode

   ---------查询编号是否存在,存在则添加错误表信息
   IF   EXISTS(select * from t_Dic_ClientAccount ca inner join #temp on ca.FNumber=#temp.FNumber)
   BEGIN
       insert into #ErrorList select '第'+CONVERT(varchar(50),#temp.RowIndex)+'条记录参考号已存在,请更改' from t_Dic_ClientAccount ca inner join #temp on ca.FNumber=#temp.FNumber
   END

   DECLARE @sql varchar(500);
    SET   @sql = 'insert into #ErrorList select ''第''+CONVERT(VARCHAR(50),RowIndex)+''条记录经销商不存在'' from #temp where RowIndex not in('+@row+')'
    EXEC(@sql)

    ---返回错误信息表（如果错误信息表为空，则数据合法）
    SELECT * FROM #ErrorList

   DROP TABLE #temp
   DROP TABLE #ErrorList
END

sql 执行批量插入（验证合法后，执行存储过程，同样传XML）

/****** Object: StoredProcedure [dbo].[sp_Import_InsertClientAccount]    Script Date: 09/26/2013 10:50:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       hefeng
-- Create date: 2013-9-25
-- Description:   批量添加经销商账目并更改金额
-- =============================================
ALTER PROCEDURE [dbo].[sp_Import_InsertClientAccount]
   @XmlStr NVARCHAR(max)
AS
BEGIN

     --DECLARE @XmlStr NVARCHAR(max)
     --SET @XmlStr='<Root>'
     --+'<Row FInComeAccount="HSBC SH" FClientName="宜兴市宜通汽车销售服务有限公司" FInComeDate="7/31/2013 8:00:00 AM" FInComeAmount="32500" FDuring="7/31/2013 8:00:00 AM" FNumber="130731BS40096555" FCreateDate="9/24/2013 5:32:32 PM" RowIndex="16" /> '
     --+'</Root>'

   DECLARE @docHandle int;
   EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlStr;
     SELECT * INTO #temp
     FROM OPENXML(@docHandle, N'Root/Row')
     WITH (FInComeAccount varchar(50),FClientName varchar(200),FInComeDate datetime,FInComeAmount decimal,FDuring varchar(50),FNumber varchar(50),FSJCode varchar(50), FCreateDate datetime,RowIndex int);
     EXEC sp_xml_removedocument @docHandle;

     ------添加表，关联添加，因为是多个表之间主键关联
   insert into t_Dic_ClientAccount select rc.ID,#temp.FInComeAccount,#temp.FInComeDate,#temp.FInComeAmount,#temp.FDuring,#temp.FNumber,#temp.FCreateDate from t_Dic_Customlist_RelationClient rc
   inner join t_Dic_Customlist_RelationClientDetail rcd on rc.ID = rcd.FID
   inner join #temp on rc.名称 = #temp.FClientName and rcd.FSJCode = #temp.FSJCode

   ------更新金额，关联更新
   update rcd set rcd.FBalance = rcd.FBalance+#temp.FInComeAmount from t_Dic_Customlist_RelationClientDetail rcd
   inner join t_Dic_Customlist_RelationClient rc on rc.ID = rcd.FID
   inner join #temp on rc.名称 = #temp.FClientName and rcd.FSJCode = #temp.FSJCode
   inner join t_Dic_ClientAccount ca on ca.FNumber =#temp.FNumber

   DROP TABLE #temp

END

这只是一个思路，简单的代码应用。可以借鉴就借鉴。。。相对于批量操作数据，还是比较简单的。