一句 sql语句搞定: Insert Into TRUCK(TruckCode,TruckName,TruckType,IcardNo,TruckCap,OperateTime)Select VEHICLE_LICENSE,TOOL_NUMBER,ATTRIBUTE3,ATTRIBUTE2,WEIGHT_MAX,LAST_UPDATE_DATE From CUX_9_FRTC_FREIGHT_TOOLS_DEF_V 这是我之前做的项目,不用解释吧,希望你有帮助
大量数据用SqlBulkCopy ,上万条记录入库
Oracle不支持也没有SqlBulkCopy类,那个只有SqlServer数据库才支持,Oracle可以用数组形式的参数批量提交来实现,但那个绝对不能叫“BulkCopy”。 OracleCommand command = new OracleCommand("链接字符串"); command.ArrayBindCount = 1000; command.CommandText = "insert into dept values(:deptno, :deptname, :loc)"; int[] deptNo = new int[1000]; string[] dname = new string[1000]; string[] loc = new string[1000]; command.Parameters.Add(new OracleParameter("deptno", OracleDbType.Int32) { Value = deptNo }); command.Parameters.Add(new OracleParameter("dname", OracleDbType.Varchar2) { Value = dname }); command.Parameters.Add(new OracleParameter("loc", OracleDbType.Varchar2) { Value = loc }); command.ExecuteNonQuery();
Oracle不支持也没有SqlBulkCopy类,那个只有SqlServer数据库才支持,Oracle可以用数组形式的参数批量提交来实现,但那个绝对不能叫“BulkCopy”。 OracleCommand command = new OracleCommand("链接字符串"); command.ArrayBindCount = 1000; command.CommandText = "insert into dept values(:deptno, :deptname, :loc)"; int[] deptNo = new int[1000]; string[] dname = new string[1000]; string[] loc = new string[1000]; command.Parameters.Add(new OracleParameter("deptno", OracleDbType.Int32) { Value = deptNo }); command.Parameters.Add(new OracleParameter("dname", OracleDbType.Varchar2) { Value = dname }); command.Parameters.Add(new OracleParameter("loc", OracleDbType.Varchar2) { Value = loc }); command.ExecuteNonQuery(); 怎么没有?Oracle.DataAccess.Client.OracleBulkCopy
insert tab col value,value1,value2,value3...value999
insert into table1(id,c1)
select 1,'aaa1' union all
select 2,'aaa1' union all
select 3,'aaa1' union all
...
select 1000,'aaa1'
提交一次
using (SqlBulkCopy sqlBC = new SqlBulkCopy(connection))
{
//一次批量的插入的数据量
sqlBC.BatchSize = dt.Rows.Count;
//超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除
sqlBC.BulkCopyTimeout = 600; //設定 NotifyAfter 属性,以便在每插入10000 条数据时,呼叫相应事件。
//sqlBC.NotifyAfter = 10000;
//sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); //设置要批量写入的表
sqlBC.DestinationTableName = 表名; //自定义的datatable和数据库的字段进行对应
sqlBC.ColumnMappings.Add("字段", "字段"); //批量写入
if (dt.Rows.Count != 0 && dt != null)
{
sqlBC.WriteToServer(dt);
dt.Dispose();
} }
Insert Into TRUCK(TruckCode,TruckName,TruckType,IcardNo,TruckCap,OperateTime)Select VEHICLE_LICENSE,TOOL_NUMBER,ATTRIBUTE3,ATTRIBUTE2,WEIGHT_MAX,LAST_UPDATE_DATE From CUX_9_FRTC_FREIGHT_TOOLS_DEF_V 这是我之前做的项目,不用解释吧,希望你有帮助
OracleCommand command = new OracleCommand("链接字符串");
command.ArrayBindCount = 1000;
command.CommandText = "insert into dept values(:deptno, :deptname, :loc)";
int[] deptNo = new int[1000];
string[] dname = new string[1000];
string[] loc = new string[1000];
command.Parameters.Add(new OracleParameter("deptno", OracleDbType.Int32) { Value = deptNo });
command.Parameters.Add(new OracleParameter("dname", OracleDbType.Varchar2) { Value = dname });
command.Parameters.Add(new OracleParameter("loc", OracleDbType.Varchar2) { Value = loc });
command.ExecuteNonQuery();
OracleCommand command = new OracleCommand("链接字符串");
command.ArrayBindCount = 1000;
command.CommandText = "insert into dept values(:deptno, :deptname, :loc)";
int[] deptNo = new int[1000];
string[] dname = new string[1000];
string[] loc = new string[1000];
command.Parameters.Add(new OracleParameter("deptno", OracleDbType.Int32) { Value = deptNo });
command.Parameters.Add(new OracleParameter("dname", OracleDbType.Varchar2) { Value = dname });
command.Parameters.Add(new OracleParameter("loc", OracleDbType.Varchar2) { Value = loc });
command.ExecuteNonQuery(); 怎么没有?Oracle.DataAccess.Client.OracleBulkCopy
insert tab col value,value1,value2,value3...value999