select query
-
In a sql stored procedure I have a select query as
"select * from Customers where Id IN(select * from MainAcc)"
This procedure is in a database named "IEAccounts" and the table Customers is also included in this database, but the MainAcc table is present in another database named "MainDb".
Now how can I query such a select statement where the two tables are in separate databases. If not then what is the other efficient way to get this done.
Thank You.
-
If the other database is linked, just refer to the table by its full name (myServer.myDatabase..myTable). If they're on the same machine, I think you can leave off the servername part, but I don't remember.
We had a problem with this once when running a 6.5 and trying to include a 7.0 (I think). We solved it with something similar to:
select * from myTable
where myCol = EXEC(select max(myCol)
from myTable2)
but I don't think I have that right. Does anyone know what I'm talking about, and remember the syntax?
HTH,
Jeff
-
alter your procedure like this:
select * from Customers where Id IN(select * from MainDb.dbo.MainAcc)
if your MainAcc's owner is not dbo replace dbo with proper owner or use MainDb..MainAcc
but it is recommended to identify table owner
BTW use join whenever posible .it has best performance:
select * from dbo.Customers C
inner join MainDb.dbo.MainAcc M
on C.Id =M.ID
-
For a more efficient query, use a join clause instead of an IN.
eg:
select cust.*
from db1.dbo.Customers cust
Inner Join db2.dbo.MainAcc main
on cust.id = main.idHope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
hey phillcart
did you see my post?your reply is exacttly similar mine!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply