devxlogo

Getting Information from MSAccess and SQLServer with One Single QUERY(T-SQL)

Getting Information from MSAccess and SQLServer with One Single QUERY(T-SQL)

The ProductMaster Table is in MSAccess and the productdescription Table is in SQLServer. Create the Example.mdb in C drive and Create the Below table in MDB(MSAccess).

 ProductMaster(MSAccess)-----------------------pid	ProductName1	maruthi2	MercedesBenz3	HondaCity4	OpelAstraproductdescription(SQLServer)pid         Prize----------- -----------1           160000$2           200000$3           300000$

To get the pid,prodcutname,prize from these two tables, execute this SQL(t-sql):

 select b.pid,b.ProductName,a.prize from productdescription a inner join(SELECT pid,ProductName  FROMOPENROWSET('Microsoft.Jet.OLEDB.4.0',_'c:example.mdb';'admin';'',[productmaster])) as b ona.pid=b.pidResult-----------pid         pname                                              prize----------- ---------------------------------------- -----------1           maruthi                                            1600002           MercedesBenz                                       2000003           HondaCity                                          300000

devx-admin

Share the Post: