January 12, 2006 at 1:22 pm
Hello, I'm trying to compare 2 tables and list the record(s) that only exists in the first table. The first table is refreshed nightly using sysdatabases from all database servers. The second table is a static snapshot.
Both tables are identical in structure:
database name (dbname)
database server name (servername)
database id (dbid)
database create date (dbcreate)
The same dbname (but with a different dbid and different servername) could exist on a different server ie: DBSERVERPROD.DB1 ... DBSERVERDEV.DB1 ... DBSERVERQA.DB1
How can I pass 2 values at the same time in the WHERE clause?
SELECT dbname, dbid, dbservername, dbcreate
FROM AuditSYSDB_DailyRefresh
WHERE dbid, dbname NOT IN (SELECT dbid, dbname FROM AuditSYSDB_Static)
...
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.
Any suggestions?
Many thanks. Jeff
January 12, 2006 at 1:41 pm
NOT EXISTS instead of NOT IN:
SELECT dbname, dbid, dbservername, dbcreate
FROM AuditSYSDB_DailyRefresh As R
WHERE NOT EXISTS (
SELECT *
FROM AuditSYSDB_Static As S
WHERE S.dbid = R.dbid
AND S.dbname=R.dbname
)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply