June 10, 2004 at 7:49 am
I am trying to find missing records between 2 tables. One table is on one database and the other table is on another database. The field u_logon_name is a primary key. Both databases are on the same server.
SELECT *
FROM abc_commerce.dbo.UserObject INNER JOIN
abc_commerce.dbo.UserObject ON test2.dbo.UserObject.u_logon_name = abc_commerce.dbo.UserObject.u_logon_name
June 10, 2004 at 8:13 am
Below script will provide missing records in db2
SELECT *
FROM db1.dbo.table1 A Left JOIN
db2.dbo.table1 B ON A.u_logon_name = B.u_logon_name
Where B.u_logon_name is null
and
Below script will provide missing records in db1
SELECT *
FROM db2.dbo.table1 A Left JOIN
db1.dbo.table1 B ON A.u_logon_name = B.u_logon_name
Where B.u_logon_name is null
1234
June 10, 2004 at 8:19 am
SELECT * FROM db1.dbo.UserObject WHERE u_logon_name NOT IN (SELECT * FROM db2.dbo.UserObject)
SELECT * FROM db2.dbo.UserObject WHERE u_logon_name NOT IN (SELECT * FROM db1.dbo.UserObject)
June 10, 2004 at 8:20 am
If you are trying to find the records that are in abc_commerce.dbo.UserObject and not in test2.dbo.UserObject try this :
Left join so you have access to all the primary tables records and then just display the ones that have null values in the second table.
SELECT *
FROM abc_commerce.dbo.UserObject
LEFT JOIN abc_commerce.dbo.UserObject ON test2.dbo.UserObject.u_logon_name = abc_commerce.dbo.UserObject.u_logon_name
Where test2.dbo.UserObject.u_logon_name IS NULL
June 11, 2004 at 12:41 am
Use a FULL JOIN
SELECT *
FROM db1.dbo.table1 A
FULL JOIN db2.dbo.table1 B
ON A.u_logon_name = B.u_logon_name
WHERE A.u_logon_name IS NULL OR B.u_logon_name IS NULL
Regards,
Leon
Regards,
Leon Bakkers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply