使用SQL查询将数据从SQLDB tbltwo插入OLEDB tblone [英] Use SQL query to INSERT data into OLEDB tblone from SQLDB tbltwo

问题描述

我有我的节目：WEBBIT

我有一个来自不同节目的数据库（只读）：很多



在WEBBIT中，我在tblpatients中有[patientID]和[LOTSID]。



我想将所有新患者从LOTS导入WEBBIT。



EG。

I have MY program: "WEBBIT"
I have a DB from a different program (read only): "LOTS"

In WEBBIT i have [patientID] and [LOTSID] in tblpatients.

I want to IMPORT all new patients from LOTS to WEBBIT.

EG.

INSERT INTO tblpatients( LOTSID, PATIENTNAME)
SELECT patients.PatientName, patients.LOTSpatientID
FROM LOTS.patients JOIN tblpatients ON patients.LOTSpatientID= tblpatients.LOTSID
WHERE (((tblpatients.LOTSID) Is Null));

相当多找到所有患者不是在Webbit中有很多并且它们将它们插入到WEBBIT中的tblpatients中。



问题是它们位于两个不同的数据库中，一个是SQL，一个是ACCDB ..



我有以下Con Strings：

Pretty much this finds all patients NOT in Webbit that ARE in LOTS and it inserts them into tblpatients in WEBBIT.

The issue is that they are in two different databases, one being SQL and one is ACCDB..

I have the following Con Strings:

SqlConnection lotscon = new SqlConnection(PackChecker.Properties.Settings.Default["LOTSConnectionString"].ToString()

OleDbConnection con = new OleDbConnection(PackChecker.Properties.Settings.Default["WebbitConnectionString"].ToString()

任何想法或方向都会令人惊叹！



我尝试了什么：


我尝试使用上面的oledbcommand使用JUST Webbit OLEDB连接，这就行了！但是这只适用于我运行Visual Studio的电脑..即使在编译之后。

这在不同的PC上工作，我不知道为什么？



当我运行它时，我尝试打开两者的连接..但是命令没有附加到两者，所以它失败了。



我有也试过下面..但我不知道从哪里开始！

Any ideas or direction would be amazing!

What I have tried:

I tried using JUST the Webbit OLEDB connection with the oledbcommand above, which WORKS! But this only works on my PC running visual Studio.. even after compiling.
This doesnt work on a different PC and i ahve no idea why??

I have tried opening a connection to both when i run it.. but the command isn't attached to both so it fails.

I have also tried the below.. but i dont know where to go from here!

DataTable dtLotsInst = new DataTable();
            using (SqlConnection lotscon = new SqlConnection(PackChecker.Properties.Settings.Default["LOTSConnectionString"].ToString()))
            {
                using (SqlDataAdapter instadapt = new SqlDataAdapter(@"SELECT * FROM NEWinstitution;", lotscon))
                {
                    lotscon.Open();

                    instadapt.Fill(dtLotsInst);

                    lotscon.Close();
                }

            }

            DataTable dtWebbitInst = new DataTable();
            using (OleDbConnection con = new OleDbConnection(PackChecker.Properties.Settings.Default["WebbitConnectionString"].ToString()))
            {
                using (OleDbDataAdapter instadaptweb = new OleDbDataAdapter(@"SELECT * FROM tblinstitution;", con))
                {
                    con.Open();

                    instadaptweb.Fill(dtWebbitInst);

                    con.Close();
                }

            }

推荐答案

我认为在另一台PC上你缺少一些先决条件，就像ACCESS dll一样。

遗憾的是，不可能将连接混合到不同的服务器上，并且只使用一个SQL语句。

你必须分别连接到两个服务器然后制作循环来遍历记录，从一个服务器读取记录并在另一个服务器中插入记录。



另见：使用LINQ Except，Intersect和Union进行数据表比较：www.dotnetmentors.com [ ^ ]

I think on the other PC you are missing some prerequisites, like ACCESS dll's.
Sadly it is not possible to mix connections to different servers, and use only one SQL statement.
You will have to connect to both servers separately and then make a loop to traverse the records, read a record from one server and insert a record in the other.

Also see: DataTable Comparison Using LINQ Except, Intersect and Union : www.dotnetmentors.com[^]

这篇关于使用SQL查询将数据从SQLDB tbltwo插入OLEDB tblone的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！