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

1、存储过程方法：

declare @customers xml

set @customers=’‘

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(‘

‘ 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(““, 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;

}

https://www.cnblogs.com/zhuawang/category/89934.html