从一个表导到另一个表中
大家可能在导数据的时候会碰到些问题,写了个windowform小程序,用来实现从一张表导数据到另一张表中.
需要说明的几个地方:
1.需要用到地方一般是你建的数据表的ID号都是通过某种机制生成的(我这里是用的一个IdFactory方法去生成的);
2.这里的导入是不做数据字段的校验的,因此一定要对应好数据,特别是数据类型和长度,以免在导的过程中报错误;
3.本来程序中是封装了两个方法的:一个是根据sql语句进行返回DataTable的查询的方法;另一个是根据sql语句执行ExecuteNoQuery的操作.这里就不贴出来了,还请多多包涵.
private void button1_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=localhost;Initial Catalog=pubs;User Id=sa;Password=sa;";
conn.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter("SELECT * FROM temp..gz",conn);
DataTable dt = new DataTable();
sqlDA.Fill(dt );
//遍历每一条记录
for(int i = 0;i < dt.Rows.Count;i++)
{
DataRow dr = dt.Rows[i]; //表示第几行的第几个字段
string sql = "INSERT INTO HrPay(Id, EmployeeName, Dept, BasePay, YearlyPay, PostPay, ReserveAllowance, FitAllowance, AddPayCar, AddPayEat, Other, Hui, TotalSum, Rent, Fee, AddPayPhone, ReduceBase, SubPayIncomeTax, SubPayAnnuities, SubPayMedicare, SubPayOutofWork, SubPayAccumulationFund, ReduceOther, ReduceSum, AddPayShould, ReserveAward, ReserveAllowance1, PostAllowance) " + //要插入到的字段名
"VALUES ('" + IdFactory.Get("Office.HrPay") + "', '" + dr["姓名"] + "', '" + dr["部门"] + "', '" + dr["基础工资"] + "', '" + dr["年功工资"] + "', '" + dr["岗位工资"] + "', '" + dr["保留补贴"] + "', '" + dr["适当补"] + "', '" + dr["远郊"] + "', '" + dr["误餐费"] + "', '" + dr["其他"] + "', '" + dr["回民"] + "', '" + dr["应发合计"] + "', '" + dr["房租"] + "', '" + dr["水电"] + "', '" + dr["电话"] + "', '" + dr["扣税基数"] + "', '" + dr["代扣税"] + "', '" + dr["养老金"] + "', '" + dr["医保"] + "', '" + dr["失业金"] + "', '" + dr["公积金"] + "', '" + dr["扣其他"] + "', '" + dr["扣款合计"] + "', '" + dr["实发合计"] + "', '" + dr["保留奖"] + "', '" + dr["保留补"] + "', '" + dr["岗位津贴"] + "')"; //将临时数据表中的数据插入到目标数据表中,其中IdFactory用来得到表中的主键
//执行ExecuteNonQuery();
conn.Close();
}
}