基于另一个数据库更新一个数据库 [英] Updating one database based off of another database

问题描述

我有一个数据库，我的所有学生信息都在，然后我有另一个数据库，我想根据第一个数据库更新信息。我有连接工作，可以提取数据，但我在比较数据时遇到问题。我不确定它是嵌套的，而不是问题或确切的问题所在。我希望将Web数据库中的所有信息更新为PS数据中的确切数据。我已经尝试切换while nots的位置（使webidrows成为第一个）仍然没有得到我想要的结果。我猜我需要更改while而不是webidrows，只需将psstunum与webstunum进行比较即可。另外，如果psstunum或webstunum为空（他们永远不应该这样，会发生什么？



所以我希望脚本抓住psstunum，搜索通过webidrows如果它在webstunum列中找到匹配项，则更新该字段。如果找不到它，我希望它插入它。



感谢任何帮助你可以给。

I've got one database that all my student information is in and then I've got another database that I'd like to update information based off the first database. I've got the connections working and can pull data but I'm having issues comparing the data. I'm not sure if it's the nested while nots or exactly where the problem lies. I want all the information in the web database to be updated to the exact data from the PS data. I've tried switching the locations of the while nots (made the webidrows first) and still not getting the results I want. I'm guessing I need to change the while not webidrows to something to just compare the psstunum to webstunum. Also, if either psstunum or webstunum are null (which they never should be, what would happen?

So I want the script to grab the psstunum, search the through the webidrows and if it finds a match in the webstunum column, updates the field. If it doesn't find it, I want it to insert it.

Thanks for any help you can give.

psconn.Open "DRIVER={Oracle in instantclient_11_2};DBQ=192.168.1.1:1521/DB1;uid=a;pwd=a;"
Set psrows = psconn.Execute("SELECT first_name,middle_name,last_name,entrydate,schoolid,ssn,enroll_status,locker_combination,grade_level,DOB,student_number FROM students")

webidconn.Open "Driver={SQL Server};Server=192.168.1.2;Database=WEB;Uid=b;Pwd=b"
Set webidrows = webidconn.Execute("SELECT student_number, DOB, First_Name, Last_Name, School_id, internet, grade FROM dbo.MCSD")

Set psStuFirstName = psrows.Fields("First_Name")
Set psStuLastName = psrows.Fields("Last_Name")
set psstunum = psrows.Fields("student_number")
Set webstunum = webidrows.Fields("student_number")
Set webStuFirstName = webidrows.Fields("First_Name")
Set webStuLastName = webidrows.Fields("Last_Name")

If isnull(psStuinet) then
else
psStuinet="Internet"
end if

webidconn.execute("UPDATE dbo.MCSD set path = '" & psStugrd & "' where Student_Number='" & psstunum & "'")

推荐答案

您不需要在循环中比较SQL数据！使用sql motor引擎。



要更新：

You don't need to compare SQL data in a loop(s)!!! Use sql motor engine.

To update:

UPDATE t1
SET t1.DOB = '12/21/2001' 
FROM dbo1.MCSD AS t1 INNER JOIN (
    SELECT student_number, ....
    FROM secondDatabase
    WHERE ... 
    ) AS t2 ON t1.student_number = t2.student_number

要插入：

To insert:

INSERT INTO dbo.MCSD (student_number) 
SELECT student_number
FROM SecondTable
WHERE student_number NOT IN (SELECT sutdent_number FROM dbo.MCSD)

尝试将Oracle服务器连接为链接服务器到MS SQL Server [ ^ ]（点击链接）并执行上面的查询。 ;）

[/ EDIT]

Try to connect Oracle server as linked server to MS SQL Server[^] (follow the link) and execute above query. ;)
[/EDIT]

这篇关于基于另一个数据库更新一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！