用xml作参数一次插入多条数据

最新推荐文章于 2023-08-20 16:27:49 发布

转载最新推荐文章于 2023-08-20 16:27:49 发布 · 611 阅读

本文探讨了使用SQL语句和存储过程在数据库中高效插入外部企业数据的方法，包括XML解析、字符串构建及批量复制技术的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.存储过程方法：

declare @customers xml
set @customers='<cus bpNo=''15'' MNo=''商户ID1'' MName=''商户名称1'' /><cus bpNo=''15'' MNo=''商户ID2'' MName=''商户名称2'' />'

INSERT control.tb_ExternalEnterprise(BPNo,EnterpriseId,EnterpriseName)
SELECT R.c.value('@bpNo','CHAR(6)'),R.c.value('@MNo','VARCHAR(50)'),
R.c.value('@MName','NVARCHAR(30)')
FROM @customers.nodes('cus') R(c)

2.sql语句方法：

INSERT tb_ExternalEnterprise(BPNo,EnterpriseId,EnterpriseName)
SELECT b.*
FROM (select cast('<cus bpNo=''15'' MNo=''商户ID1'' MName=''商户名称1''/>
     <cus bpNo=''15'' MNo=''商户ID2'' MName=''商户名称2'' />' as xml) a)t
cross apply(select R.c.value('@bpNo','CHAR(6)') as bpNo,
                   R.c.value('@MNo','VARCHAR(50)') as MNo,
                   R.c.value('@MName','NVARCHAR(30)') as MName
            from t.a.nodes('cus') R(c)) b

System.Text.StringBuilder sb = new System.Text.StringBuilder("'");
            for (int i = 0; i < count; i++)
            {
                sb.AppendFormat("<cus bpNo='{0}' />", i.ToString());//Guid.NewGuid().ToString());
            }
            sb.Append("'");

************************* SqlBulkCopy ********************************

private static long SqlBulkCopyInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            DataTable dataTable = GetTableSchema();
            string passportKey;
            for (int i = 0; i < count; i++)
            {
                passportKey = i.ToString();//Guid.NewGuid().ToString();
                DataRow dataRow = dataTable.NewRow();
                dataRow[0] = passportKey;
                dataTable.Rows.Add(dataRow);
            }

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
            sqlBulkCopy.DestinationTableName = "Passport";
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            sqlBulkCopy.Close();
            sqlConnection.Close();

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }