批量数据插入表（SQL和代码）（DataTabel到表）

一、代码批量插入

实现1：

代码批量（DataTable）插入表，注意：DataTable 列名必须和数据库表列名大小写一致，表名和DataTable表名相同；

public bool SqlBulkCopyByDatatable(DataTable dt,ref string emsg)
        {
            try
            {
                SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(SqlHelper.connStr, SqlBulkCopyOptions.UseInternalTransaction);
                sqlbulkcopy.DestinationTableName = dt.TableName;
                foreach (DataColumn dc in dt.Columns)
                {
                    sqlbulkcopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                }
                sqlbulkcopy.WriteToServer(dt);
                return true;
            }
            catch (Exception ex)
            {
                emsg = ex.ToString();
                return false;
            }
        }

实现2

       public void InsertRecord(DataTable dt)
        {
            SqlBulkCopyColumnMapping CopyColumnMapping1 = new SqlBulkCopyColumnMapping("code", "code");
            SqlBulkCopyColumnMapping CopyColumnMapping2 = new SqlBulkCopyColumnMapping("type", "type");
            SqlBulkCopyColumnMapping CopyColumnMapping3 = new SqlBulkCopyColumnMapping("palletno", "palletno");


            SqlBulkCopyColumnMapping CopyColumnMapping4 = new SqlBulkCopyColumnMapping("set_label", "set_label");
            SqlBulkCopyColumnMapping CopyColumnMapping5 = new SqlBulkCopyColumnMapping("paedl_ship_limit", "paedl_ship_limit");
            SqlBulkCopyColumnMapping CopyColumnMapping6 = new SqlBulkCopyColumnMapping("flag", "flag");
            SqlBulkCopyColumnMapping CopyColumnMapping7 = new SqlBulkCopyColumnMapping("qty", "qty");

            SqlBulkCopyColumnMapping CopyColumnMapping8 = new SqlBulkCopyColumnMapping("custid", "custid");
            SqlBulkCopyColumnMapping CopyColumnMapping9 = new SqlBulkCopyColumnMapping("compid", "compid");
            SqlBulkCopyColumnMapping CopyColumnMapping10 = new SqlBulkCopyColumnMapping("stockid", "stockid");
            SqlBulkCopyColumnMapping CopyColumnMapping11 = new SqlBulkCopyColumnMapping("planid", "planid");
            SqlBulkCopyColumnMapping CopyColumnMapping12 = new SqlBulkCopyColumnMapping("houseid", "houseid");
            SqlBulkCopyColumnMapping CopyColumnMapping13 = new SqlBulkCopyColumnMapping("JIT_SHIP_LIMIT", "JIT_SHIP_LIMIT");
            SqlBulkCopyColumnMapping CopyColumnMapping14 = new SqlBulkCopyColumnMapping("outinflag", "outinflag");
            SqlBulkCopyColumnMapping CopyColumnMapping15 = new SqlBulkCopyColumnMapping("WH_ENTER_TIME", "WH_ENTER_TIME");
            SqlBulkCopyColumnMapping CopyColumnMapping16 = new SqlBulkCopyColumnMapping("isInternal", "isInternal");

            using (SqlBulkCopy sbc = new SqlBulkCopy(sqlSerH.connStr, SqlBulkCopyOptions.Default))
            {
                sbc.ColumnMappings.Add(CopyColumnMapping1);
                sbc.ColumnMappings.Add(CopyColumnMapping2);
                sbc.ColumnMappings.Add(CopyColumnMapping3);
                sbc.ColumnMappings.Add(CopyColumnMapping4);
                sbc.ColumnMappings.Add(CopyColumnMapping5);
                sbc.ColumnMappings.Add(CopyColumnMapping6);
                sbc.ColumnMappings.Add(CopyColumnMapping7);
                sbc.ColumnMappings.Add(CopyColumnMapping8);
                sbc.ColumnMappings.Add(CopyColumnMapping9);
                sbc.ColumnMappings.Add(CopyColumnMapping10);
                sbc.ColumnMappings.Add(CopyColumnMapping11);
                sbc.ColumnMappings.Add(CopyColumnMapping12);
                sbc.ColumnMappings.Add(CopyColumnMapping13);
                sbc.ColumnMappings.Add(CopyColumnMapping14);
                sbc.ColumnMappings.Add(CopyColumnMapping15);
                sbc.ColumnMappings.Add(CopyColumnMapping16);
                //数据库表名
                sbc.DestinationTableName = "lms_paedl_interface";
                sbc.WriteToServer(dt);

            }

二、数据库SQL批量插入
Oracle
//Oracle批量插入 dual为系统参数

//Oracle批量插入 dual为系统参数
insert into Test(name,Value)
select '1','1'
from dual
union all
select '2','2'
from dual
union all
select '3','3'
from dual
union all
select '3','3'from dual

SqlServer 批量插入

insert into ys (Name,Value) values
('1','1'),
('2','2'),
('2','2'),
('3','3'),
('4','4')