Compare two tables in different database.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Compare two tables in different database.

Author

Topic

arunpundir78
Starting Member

7 Posts

Posted - 2009-07-21 : 07:34:45

Hi All -

I am new to SQL Server and currently i want to compare two tables in different database but on the same server.

Two tables are having the same name as - History in different database - 1-STRINGDev and 2-STRINGQA

I am using SQL Server 2005 and Now when i connect to sql server and
select one db-Say StringQA DB and write the following query -

SELECT * FROM STRINGDev.History
MINUS
SELECT * FROM StringQA.History

Then it is throwing the following error -

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'STRINGDev.History'.

Note:- If i select the StringDev db then System throw the following error -
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'STRINGQA.History'.

Kindly Help.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-21 : 08:00:15

use EXCEPT and INTERSECT operators

r select sh.* from stringdev.history sh leftjoin stringqa qa on qa.id = sh.id
where qa.id is null

arunpundir78
Starting Member

7 Posts

Posted - 2009-07-21 : 08:26:35

After running the query mentioned i am getting the error -
Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'leftjoin'.

select sh.* from stringdev.history sh leftjoin stringqa.history on
qa.id = sh.id where qa.id is null

Kindly help.
Why i am not able to execute the query sucessfully when it is reffering to two different database on the same server ? :(

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 08:32:53

leftjoin should be left join

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-21 : 08:32:53

select sh.* from stringdev.history sh left join stringqa.history as qa on
qa.id = sh.id where qa.id is null

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 08:33:59

quote:
Originally posted by bklr

select sh.* from stringdev.history sh left join stringqa.history as qa on
qa.id = sh.id where qa.id is null

What a tie !!!

Madhivanan

Failing to plan is Planning to fail

arunpundir78
Starting Member

7 Posts

Posted - 2009-07-21 : 08:44:17

Hello All -

I am still getting the same error.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'STRINGDev.History'.

I want to highlight one thing -

When i log in into the SQL Server 2000 then by default master db is selected and after executing the query i am getting error.

If i select any of the DB Instance - STRINGQA or STRINGDev instance and execute the query then System throw the same error.

Is it related to some permissions or some setting etc?

arunpundir78
Starting Member

7 Posts

Posted - 2009-07-21 : 10:39:09

Any Help on this. Kindly let me know your views if i have to check out something. Please help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-22 : 02:26:06

STRINGDev.History

should be

STRINGDev..History

Madhivanan

Failing to plan is Planning to fail

arunpundir78
Starting Member

7 Posts

Posted - 2009-07-23 : 01:14:02

Thanks Madhivanan! Using stringdev..history worked and i am able to access the tables in other db when i am in stringqa db instance.

But when i run the query to compare the two tables for any difference -
select sh.* from stringdev..history sh left join stringqa.history as qa on qa.department = sh.department where qa.department is not null

then it is not giving the correct result.

I want to find the difference in "history" table which is existing in stringdev and stringqa db instances and deliberately i updated the department field value in "history" table of stringqa db instances.
So after running the above query i am not getting the expected result.

If i use the query -
select sh.* from stringdev..history sh left join stringqa.history as qa on qa.id = sh.id where qa.id is not null
then it is not displaying any difference in two tables whereas there is.

Kindly Help.

arunpundir78
Starting Member

7 Posts

Posted - 2009-07-27 : 11:16:35

Any Help on this ???
Kindly guide me :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources