Two tables on two different database
-
Using select from two tables on two different databases
Hi all,
On Ms SQL server 2000 we have two databases 1.Prod with a table called A and 2. Dev with a table called B.
How do I run select statement from both databases?
Select prod.A.*, dev.B.* from prod, dev
Where prod.a.id = dev.b.id
The above select does not work.
Thanks for any help.:)
Abrahim
-
When you use two part naming (prod.A in your example) SQL thinks that "prod" is a reference to a schema. Use three part naming : [databasename].[schemaname].[tablename]
For your purposes, it's probably prod.dbo.A if the example tables are in the dbo schema.
-
Just so we can all get into the right habits - use the three-part names like Matt Stockham pointed to, but do so only in the FROM clause, assigning an alias to the table.
So - in your example:
SELECT
ProdA.*,
DevB.*
FROM
Prod.dbo.A as ProdA
INNER JOIN Dev.dbo.B as DevB
ON ProdA.id=DevB.id
Anything over 2 parts used pretty much anywhere other than the FROM clause is now deprecated.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I tried:
select prod.dbo.A.*, dev.dbo.B.*
from prod, dev
where prod.dbo.A.id = dev.dbo.B.id
and prod.dbo.A.id <= 100
but getting error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'prod'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dev'.
Thanks,:blush:
Abrahim
-
That did it!!!
Thanks:D
-
Did you use the alias?
-
Here is the real code:
select cp2abc.*
from prd2.dbo.abc as prd2abc, prd1.dbo.abc as prd1abc
where not exists
(select prd1abc.subj_num from prd1.dbo.abc as prd1abc where prd2abc.subj_num = prd1abc.subj_num
and prd2abc.abc_DATE = prd1abc.abc_date)
Abrahim:Whistling:
-
In your query if you write like "prod.dbo.tablename" then SQL server take it like prod as ur datbase name,dbo as schema and tablename as table name you are trying to select.
so here it will take "prod" as your database name, not your server name...You need to specify linked server for that.
-
The query is for two tables on two different databases on the same server, if I'm not wrong. So there is no need for linked servers.
-
In my example both database are on the same Server.
Abrahim
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply