Is this possible?

Hi,

I want to write some t-sql that selects data from a different database from which the tsql is being written in, some like this:

there are 2 databases A and B

I want to select the unit price from database A where the ID feild of database A is equal to the ID feild of database B

kinda something like this pseudo code:

select UNIT_PRICE

FROM Prices

WHERE Prices.Product_ID = (DATABASE B)Prices.Product_ID

can somebody please give me an example of how this can be done

Many Thanks

Try:

select unit_price

from dbo.prices as a

where product_id in (select b.product_id from another_db.dbo.prices as b)

-- or

select unit_price

from dbo.prices as a

where exists (select * from another_db.dbo.prices.product_id as b where b.product_id = a.product_id)

-- or

select a.unit_price

from dbo.prices as a inner join another_db.dbo.prices as b

on a.product_id = b.product_id

AMB

|||

But before executing the query you have to setup those remote database server as linked server in your current database.

here the sample code to setup the linked server..

Code Snippet

EXEC sp_addlinkedserver

@.server = 'ServerBAliasName',

@.provider = 'SQLOLEDB.1',

@.srvproduct = '',

@.provstr = 'Privider=SQLOLEDB.1;Data Source=ServerB;Initial Catalog=Database'

Exec sp_addlinkedsrvlogin

@.rmtsrvname = 'ServerBAliasName',

@.useself = true,

@.locallogin = null,

@.rmtuser = 'Userid',

@.rmtpassword = 'Password'

--Later you can use any of the above query to get the result

|||

Good point Manivannan, but just if they reside in a different server.

AMB

|||Yes... if it is on different servers then we should have the Linked Server|||Thanks for you help guys. It is much appreciated.