从一个 Access读取的数据怎么上传到Sql中？

Question

本帖最后由 comszmz 于 2013-06-27 21:54:21 编辑

Answer 1

你可以编程获得一个Access数据库的表结构和字段列表，然后编写成一个通用的导入程序。

Answer 2

用SQL导入功能，选数据源为Access 找到你的Access文件夹，勾选要导的表。。即可

Answer 3

１、直接用SQL导入向导，但字段一定要对应正确
２、查询出Access表中数据，然后循环插入，其实和从EXCEL表中取数据，插入SQL数据库一个道理。

Answer 4

    private void upload()
        {
            if (connMdb == null)
            {
                openDBC();
            }
            if (connMdb.State != ConnectionState.Open)
            {
                openDBC();
            }
            //================================
            string sqlStr = "select * from XM where ISPOST=0";
            OleDbCommand Dbcom = new OleDbCommand(sqlStr, connMdb);
            OleDbDataReader dbDR = Dbcom.ExecuteReader();
            if (int.Parse(textBox3.Text) <= 0)
            {
                MessageBox.Show(this, "没有可被上传的数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }            int iCount = 0;
            if (is_connection)
            {
                string sqlStr1="select * from "+Program.sqlConn.Table1;
                SqlCommand DbcomSQL = new SqlCommand(sqlStr1, conn);
                while (dbDR.Read())
                {
                    sqlStr1 = "INSERT INTO " + Program.sqlConn.Table1 + "(BarCode,FDate,T01,T02,T03,T04,T05,T06,T07,T08,T09,T10,T11,T12,T13,T14,T15,T16,T17,T18,Status,ISPOST)";
                    sqlStr1 += " VALUES('"+dbDR["BarCode"].ToString()+"','";
                    sqlStr1 += dbDR["postdate"].ToString() + "',";
                    sqlStr1 += dbDR["T01"].ToString() + ",";
                    sqlStr1 += dbDR["T02"].ToString() + ",";
                    sqlStr1 += dbDR["T03"].ToString() + ",";
                    sqlStr1 += dbDR["T04"].ToString() + ",";
                    sqlStr1 += dbDR["T05"].ToString() + ",";
                    sqlStr1 += dbDR["T06"].ToString() + ",";
                    sqlStr1 += dbDR["T07"].ToString() + ",";
                    sqlStr1 += dbDR["T08"].ToString() + ",";
                    sqlStr1 += dbDR["T09"].ToString() + ",";
                    sqlStr1 += dbDR["T10"].ToString() + ",";
                    sqlStr1 += dbDR["T11"].ToString() + ",";
                    sqlStr1 += dbDR["T12"].ToString() + ",";
                    sqlStr1 += dbDR["T13"].ToString() + ",";
                    sqlStr1 += dbDR["T14"].ToString() + ",";
                    sqlStr1 += dbDR["T15"].ToString() + ",";
                    sqlStr1 += dbDR["T16"].ToString() + ",";
                    sqlStr1 += dbDR["T17"].ToString() + ",";
                    sqlStr1 += dbDR["T18"].ToString() + ",";
                    sqlStr1 += dbDR["Status"].ToString() + ",";
                    sqlStr1 += "1)";
                    DbcomSQL.CommandText = sqlStr1 ;
                    DbcomSQL.ExecuteNonQuery();
                   iCount++;
                }
                dbDR.Close();
                sqlStr = "UPDATE XM SET ISPOST=1 where ISPOST=0";
                Dbcom.CommandText = sqlStr;
                int iRet = Dbcom.ExecuteNonQuery();                sqlStr = "SELECT * FROM XM where ISPOST=0";
                Dbcom.CommandText = sqlStr;
                textBox3.Text = Dbcom.ExecuteNonQuery().ToString();
                if (int.Parse(textBox3.Text) > 0)
                {
                    btnUpload.Enabled = true;
                }
                else
                {
                    btnUpload.Enabled = false;
                }
                MessageBox.Show(this, "数据上传完成！共:" + iCount.ToString() + "条记录被上传!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                textBox1.Text = "0";
                btnUpload.Enabled = false;
            }
            else
            {
                MessageBox.Show(this, "远程数据库连接失败,不能将本地数据上传到服务器！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }        }这是我的代码，要用每一句循环来插入这样效率可能会低很多，有没有一次性将OleDbDataReader dbDR的结果传到SQL中呢？

Answer 5

一句话就搞定的事情 何必写这么多代码..
insert into xxoo
select * from 
openrowset(
'Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=Yes;IMEX=1;Database=D:\6总装任务.xlsx',
'select * from [0ETC100295$]')
这个是查询xls的 自己百度下 稍微修改下 就直接查询access了

Answer 6

还有比较简单的 就是使用SqlBulkCopy这个东西了 也很快

Answer 7

不行的，试了用Openrowset，说是SQL2005安全保护之类，不支持这类驱动。而且一般要服务器端也要支持这个功能才行.
这个是查询xls的 自己百度下 稍微修改下 就直接查询access了