Populate Dataset with tables from multiple databases

R

rmatthew

Well-known member
Joined
Dec 30, 2002
Messages
115
Location
Texas
Hello folks!

This is my first post here so hope I follow convention:

What I am attempting to do is to gather data from multiple tables from 2 different databases into one dataset. Does anybody have a suggestion.

Thank you in advance.
 
Are you using SQL Server? You can join across databases in the JOIN clause in your SQL SELECT statement, such as:

Code: 
SELECT t1.Column1, t2.Column2
FROM Database1..Table1 t1
INNER JOIN Database2..Table2 t2 ON t1.KeyCol = t2.KeyCol

The above uses the ".." syntax, which says skip the owner (usually dbo) of the table, more or less.

If the databases are on different servers, you can set up a link server and still do the above.

If you cant do the above, you can use the DataSets Merge method to merge 2 different DataSets into one.

-ner
 
Actually the project spec. should be that the program pulls datasets from two different databasese (possibly of different types - via a ODBC connection - 1 could be access - 1 could be SQL etc.) I need to combine tables from them.

What has happened is that during the years the customer has migrated some data and not others. They have moved tables etc. between here and there. For my particular application I am using two Access databases BUT I can see it comming where I will need to modify it to access to dissimilar ones.
 
I believe your only hope is to link the other odbc datasources to
either Access (linked table) or SQL Server (linked server). If it
requires separate connection strings, you arent going to get the
data into one dataset.
 
As I said above, you could always use the Merge method, but you wouldnt be able to Update the database when you were through - it would only allow you to view the two different tables at once through one DataSet. After the tables (or DataSets) are merged, you could manually add a DataRelation to implement a heirarchical view or whatever you needed. If you need both tables data to be seen as one table, you can merge the individual rows into one DataTable - similar to a fancy UNION :)

-ner
 
Looks as if merging the datasets and creating the relation is the way to go. I do need the union to get one table/dataset if possible. Can you run a query on a merged dataset and create another dataset/table (like if the tables where in the same database to start with?

example:

Pull table1 from database1 into a dataset1
Pull table2 from database2 into a dataset2
Merge dataset2 into dataset1

Query the merged dataset (dataset1) to create a union type query into a third dataset and dispose of the prior 2?
 
If I understand you, your "query" of the DataSet is to get them into one DataTable? That should be what Merge does - copies rows from one DataTable (say table2) into another DataTable (say table1). You have options of what to do when the columns arent exactly the same (ignore, replace, etc.).

From your "example", whats wrong with disposing of DataSet2 and just using DataSet1? What kind of query are you trying to do?

If its to filter rows, you could use a RowFilter (if its a fairly simple WHERE clause type of filter).

-ner
 
I have a table what has personal information it contains things like firts, middle and last name, employee id, etc.

I have another table that contains some other information but only has their employee id.

Both of these tables are in completely different databases.

What I want to do is add the first and last name etc. fields with the other information.


NOW - this is simplified as there are actually 4-5 tables from different databases that need to eventually end up togeather.

there doesnt need to be any edits or updates to the database. It is strictly for viewing / reporting.
 
Ah, sounds like youll need to write your own "merge" type of function. Nothing built into DataSets will do what you want directly.

Youll probably have to loop through the columns of table2, add them to table1. Then loop through the rows of table2, filter table1 based on the employeeID of table2, then add the data from table2 to table1 manually.

Have fun!
-Nerseus
 
Back
Top