insert into Table (col1,col2,col3) values (val1,val2,val3) insert into Table (col1,col2,col3) values (val1,val2,val3) …… insert into Table (col1,col2,col3) values (val1,val2,val3) 和 insert into Table (col1,col2,col3) values (val1,val2,val3),(val1,val2,val3),……(val1,val2,val3)有区别吗?
该表在这儿只是演示使用,所以只提供了最基本的字段 现在往表中插入几条记录 insert into Counter select '127.0.0.1',getdate(),1 union all select '127.0.0.2',getdate(),1 union all select '127.0.0.3',getdate(),1
批量插入,每2000行提交一次 public void BulkCopy(string tableName, DataTable dt) { using (SqlConnection conn = new SqlConnection(_connString)) { conn.Open(); using (SqlTransaction transaction = conn.BeginTransaction()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction)) { bulkCopy.BatchSize = 2000; bulkCopy.BulkCopyTimeout = _CommandTimeOut; bulkCopy.DestinationTableName = tableName; try { foreach (DataColumn col in dt.Columns) { bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); } bulkCopy.WriteToServer(dt); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); base.ThrowException(ex); } finally { conn.Close(); } } } } }
insert into Table (col1,col2,col3) values (val1,val2,val3)
……
insert into Table (col1,col2,col3) values (val1,val2,val3)
和
insert into Table (col1,col2,col3) values (val1,val2,val3),(val1,val2,val3),……(val1,val2,val3)有区别吗?
该表在这儿只是演示使用,所以只提供了最基本的字段
现在往表中插入几条记录
insert into Counter
select '127.0.0.1',getdate(),1 union all
select '127.0.0.2',getdate(),1 union all
select '127.0.0.3',getdate(),1
public void BulkCopy(string tableName, DataTable dt)
{
using (SqlConnection conn = new SqlConnection(_connString))
{
conn.Open();
using (SqlTransaction transaction = conn.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.BatchSize = 2000;
bulkCopy.BulkCopyTimeout = _CommandTimeOut;
bulkCopy.DestinationTableName = tableName;
try
{
foreach (DataColumn col in dt.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.WriteToServer(dt);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
base.ThrowException(ex);
}
finally
{
conn.Close();
}
}
}
}
}