Related Tables in Different Databases; how to query

  • Thread starter Thread starter Ed Warren
  • Start date Start date
E

Ed Warren

Database1 contains a table of people with addresses (sql Server)
Table: People
MasterID MasterName MasterAddress1 etc.


DataBase2 Contains a table of people and the property they own. (msAccess ->
imported from the sqlServer but detached)
Table Property
PropertyID MasterID PropertyDescription, etc.


I can easily put together oledbdataadaptors to fill a common dataset
(dataset1) with:

People Table
and
Property Table

My task is to generate a table/view something like

SELECT Property.PropertyDescription, People.MasterName, People.MasterAddress
FROM Property INNER JOIN People ON Property.MasterID = People.MasterID;

I'm at a loss as to what Ado.net widget, and/or process to use to do this.
It would be easy if they came from the same database (they sorta do but we
can't go there) ;>.

The only approach I can dream up is to create a temp table in one of the
databases of the data from the other then do a query, but for several
reasons, I would like to find a more 'elegant' way to do this.

Ed Warren.
 
You can solve this in at least two manners.

1. Let SQL server bridge the connection between the two databases. If they
are on two seperate servers, us a linked server. Pick one database to host
as director for data operations. Using stored procedures manipulate the
data as you need. You can use views as long as both primary keys are
present, scripting the database can become tedious though.

Local Server 2 Databases

SELECT Property.PropertyDescription, People.MasterName,
People.MasterAddress
FROM database1.dbo.Property Property INNER JOIN Database2.dbo.People
People ON Property.MasterID = People.MasterID;

2 SQL Servers 2 Databases (Server1 contains the People table in Database1
and links to Server 2)

--Server2 is a linked server
-- to grant access to Database 2's Property Table
--refer to sp_addlinkedserver in help docs

SELECT Property.PropertyDescription, People.MasterName,
People.MasterAddress
FROM server2.database1.dbo.Property Property INNER JOIN
Database2.dbo.People People ON
Property.MasterID = People.MasterID;


2. Make a call a call to each database, and then populate a datatable
accordingly. All DML will be manual.


adp1.fill(peopletable)
adp2.fill(propertytable)

for each dr1 as datarow in peopletable.rows
for each dr2 as datarow in propertytable.rows
set drMixed = mydataset.mixedtable.newmixedrow
with drmixed
.MasterName = dr1.MasterName
.PropertyDescription = dr2.PropertyDescription
end with
mydataset.mixedtable.addrow(drmixed)
next dr2
next

'Accept the changes so that they are not flagged when evaluating for
later updates
mydataset.mixedtable.acceptchanges

dim dt as dataset.MixedDatatable

dt = mydataset.MixedTable.Getchanges(Updated or Inserted or Deleted)

for each dr as datarow in dt.row
if dr.rowstate = Updated then
'Im sure we could test what was changed
ssql = "Update people set.... where.."
cmdPeople.execute ssql
ssql = "Update properties set... where"
cmdProperties.execute ssql
end if
next dr

HTH
 
Thanks for your rapid response. Option 2 looks like where I need to go. I
was afraid it would not be easy. You have provided a good roadmap.

Again Thanks,

Ed Warren.
 
Back
Top