DataTable tb1 = ...;//从access读出 DataTable tb2 = ...;//从sql读出; foreach(DataRow dr in tb1.Rows) tb2.Rows.Add(dr.ItemArray);yourDataAdapter.Update(tb2);
//assume you have retrieved the data From Access DataSource //and loaded its into a DataTable(AccessOrderDt) //assuming relatively table's name of SqlServer is SqlOrder//in the sqlOrder Table you should include a primary Key SqlCommand comd = new SqlCommnd("select f1,f2,.. from SqlOrder where 1=0",yourSqlConnObj); SqlDataAdapter adp = new SqlDataAdapter(comd);DataTable SqlOrderDt = new DataTable(); adp.Fill(SqlOrderDt);DataRow tempDr; foreach(DataRow dr in AccessOrderDt.Rows) { tempDr = SqlOrderDt.NewRow(); tempDr[f1] = dr["id"]; tempDr[f2] = dr["pono"]; //set the other columns value here }SqlOrderDt.AcceptChanges(); adp.update();
string Sql = "SqlServer查询"; string Sql1 = "Access查询"; SqlCommand cmd1 = new SqlCommand(Sql); da.SelectCommand = cmd1; DataSet ds = new DataSet; DataTable NewSqlServer; da = new SqlDataAdapter(SqlNewTable,Conn1); da.Fill(ds,"SqlServer表"); ServerTable = ds.Tables["SqlServer表"];//得到表结构 cmd1 = new SqlCommand(Sql1); da.SelectCommand = cmd1; DataTable ATable; da = new SqlDataAdapter(ds,Conn1); da.Fill(ds,"Access表"); ATable = ds.Tables["ATable"];//得到Access数据库表的数据 if(ATable.Rows.Count != 0) { DataRow NewRow; for(int i=0;i<ATable.Rows.Count;i++) { NewRow = NewSqlServer.NewRow(); NewRow["ID"] = ATable.Rows[i]["ID"]; NewRow["PONO"] = ATable.Rows[i]["PONO"]; NewSqlServer.Rows.Add(NewRow); } } //在这里写入你的insert语句可以是一个存储过程; da.InsertCommand = cmd; da.Update(NewSqlServer); da.Dispose(); da = null; Conn1.Close(); Conn1.Dispose();
DataTable tb2 = ...;//从sql读出;
foreach(DataRow dr in tb1.Rows)
tb2.Rows.Add(dr.ItemArray);yourDataAdapter.Update(tb2);
//and loaded its into a DataTable(AccessOrderDt)
//assuming relatively table's name of SqlServer is SqlOrder//in the sqlOrder Table you should include a primary Key
SqlCommand comd = new SqlCommnd("select f1,f2,.. from SqlOrder where 1=0",yourSqlConnObj);
SqlDataAdapter adp = new SqlDataAdapter(comd);DataTable SqlOrderDt = new DataTable();
adp.Fill(SqlOrderDt);DataRow tempDr;
foreach(DataRow dr in AccessOrderDt.Rows)
{
tempDr = SqlOrderDt.NewRow();
tempDr[f1] = dr["id"];
tempDr[f2] = dr["pono"];
//set the other columns value here
}SqlOrderDt.AcceptChanges();
adp.update();
string Sql1 = "Access查询";
SqlCommand cmd1 = new SqlCommand(Sql);
da.SelectCommand = cmd1;
DataSet ds = new DataSet;
DataTable NewSqlServer;
da = new SqlDataAdapter(SqlNewTable,Conn1);
da.Fill(ds,"SqlServer表");
ServerTable = ds.Tables["SqlServer表"];//得到表结构
cmd1 = new SqlCommand(Sql1);
da.SelectCommand = cmd1;
DataTable ATable;
da = new SqlDataAdapter(ds,Conn1);
da.Fill(ds,"Access表");
ATable = ds.Tables["ATable"];//得到Access数据库表的数据
if(ATable.Rows.Count != 0)
{
DataRow NewRow;
for(int i=0;i<ATable.Rows.Count;i++)
{
NewRow = NewSqlServer.NewRow();
NewRow["ID"] = ATable.Rows[i]["ID"];
NewRow["PONO"] = ATable.Rows[i]["PONO"];
NewSqlServer.Rows.Add(NewRow);
}
}
//在这里写入你的insert语句可以是一个存储过程;
da.InsertCommand = cmd;
da.Update(NewSqlServer);
da.Dispose();
da = null;
Conn1.Close();
Conn1.Dispose();
建议你不要用COMMANDBUILDER而自己写SQL语句.
serverTable其实呵呵写错了,应该是NewSqlServer代表的是你从SqlServer数据库中得到的DataTable
ATable则代表你从Access数据库中得到的DataTable
由于两个数据库中的结构和字段类型一样,所以可以用DataRow来添加
最后使用DataAdapter的update方法把新添加的数据一次推入数据库中
你用的方法要考虑数据库连接次数问题,如果次数过多sqlserver也许会崩溃.