Updating one database based off of another database

Question

0.00/5 (No votes)

See more:

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.

VB

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 & "'")

Posted 11-Jul-13 9:30am

allenflame

Updated 25-Jul-13 4:36am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-17T12:12:00

Solution 1

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

To update:

SQL

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:

SQL

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

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

Posted 17-Jul-13 12:12pm

Maciej Los

Updated 25-Jul-13 4:38am

v2

Comments

allenflame 25-Jul-13 10:16am

I see what you did but these are two different databases on two different servers. One is Oracle, the other SQL.

webidconn.execute("UPDATE dbo.MCSD set path = grade_level where First_name='Dwight'")

grade_level is in the Oracle database named students, and dbo.MCSD is the second. I tried doing students.grade_level. Wouldn't I still need one while do?

Maciej Los 25-Jul-13 10:22am

OK, now i understand. Please, next time be more specific and provide more details. I'll try to help you. Please, remaind me if i'll forget.

allenflame 25-Jul-13 10:38am

Thanks Maciej. I updated the code some in the question, but if needed, I have the original code also. Thanks again for all your help with this.

Maciej Los 25-Jul-13 10:39am

See my updated answer ;)
There is a chance to it without loops ;)

allenflame 5-Aug-13 13:19pm

Ok, couldn't really get the oracle client to work right on the sql server. What I did was now I create a new database on the SQL server and insert all the current data to it. Works great. Going to play around with the update/insert now from databases on the same server.

allenflame 5-Aug-13 15:38pm

webidconn.execute("UPDATE dbo.pspull set firstname = '" & psStuFirstNamea & "' where studentid = '" & psStunum & "'")

Don't even need the second database, but I'm using it for testing. I'm having a time with apostrophes. If a student has one in their name, I get errors, cutting on error resume next works, but I'm thinking there are probably 50-100 kids with apostrophes in their names.

I tried Set psStuLastNamea = Replace(psStuLastName, "'", "''"), then I get object required: string. If I take that out, I get incorrect syntax near psStulastname. I also tried adding apostrephes and quotes in the update command, more errors. Any ideas?