Comparing Records in two identical Database

[joelo]

Please could anyone help me out on how to Compare Records in two identical tables in diferent Database and copy any records that exist in table1 but does not exist in table2 to Table3 (identical) in another Database...

Please really don't know where to start from.....kindly help me out.

Thanks in advance

[CardboardHammer]

Exact details depend on what DBMS(s) you're using.

You'll want something along the lines of:

INSERT INTO db3.Tablewhatever
SELECT T1.* FROM
db1.Tablewhatever T1 LEFT JOIN db2.Tablewhatever T2
ON T1.primarykeyfield(s) = T2.primarykeyfield(s)
WHERE T2.memberofprimarykey IS NULL


Note that it will be uglier if there is no primary key and exact duplicate rows are allowed.

I'm guesing DB1 is "live", DB2 is a "primary backup" and DB3 is an "incremental backup", or something along those lines?

[joelo]

Could anyone out....I don't know what I am doing wrong

I keep getting the following error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/maintenance/compliance/bsldatabasenottestedlist.asp, line 148

strsql = "SELECT * FROM [bsldatabase] IN ("& server.mappath("db\Database.mdb"); LEFT JOIN [bsldatabase].DEVICEID ON [bsldatabase].DEVICEID IN ("& server.mappath("db\Database.mdb") & ") = [bsldatabase].DEVICEID WHERE [bsldatabase].DEVICEID IS NULL"
------------------------------------------------------------------------------^



strsql = "SELECT * FROM [bsldatabase] IN ("& server.mappath("db\Database.mdb") LEFT JOIN [bsldatabase].DEVICEID ON [bsldatabase].DEVICEID IN ("& server.mappath("db\Database.mdb") & ") = [bsldatabase].DEVICEID WHERE [bsldatabase].DEVICEID IS NULL"

[CardboardHammer]

Umm... You're doing a self-join (whether or not you actually meant to) and, as written, there's no way to tell which DEVICEID you're talking about... I believe you need to scrap that query and start over, as I don't think it's anywhere near to what you're looking for...

[joelo]

how about this ...?


Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'bsldatabase.DEVICEID = bsldatabase.DEVICEID [IN (F:\wwwdata\maintenance\compliance\dbtemp\Databasetemp.mdb)]'.

/maintenance/compliance/bsldatabasenottestedlist.asp, line 170



strsql = "SELECT * FROM bsldatabase LEFT JOIN bsldatabase [IN ("& server.mappath("dbtemp\Databasetemp.mdb") &")] ON bsldatabase.DEVICEID = bsldatabase.DEVICEID [IN ("& server.mappath("dbtemp\Databasetemp.mdb") &")] WHERE bsldatabase.DEVICEID IS NULL"