This project is read-only.

CONCEPT: Using data links to query multi-databases

Apr 22, 2010 at 10:15 PM
Edited Apr 25, 2010 at 12:18 AM

This is a concept.  Any ideas, thoughts or comments are certainly welcome.

This concept was based on a conversation while going home in the Metrolink Train.  A good 'train buddy' of mine were talking about working with multiple data sources.  And this idea came up.  And this was to be able to query multiple data sources (access, sql, oracle, dbf, etc...) using a single tool.

 

@setlink S1 provider=SqlClient connectionString=server=(local); integrated security=true; database=Northwind
@setlink S2 provider=OracleClient connectionString=server=FOO; integrated security=true; database=bar
@setlink S3 file=c:\accessfile.mdb
@setlink S4 file=c:\textfile.csv

select *
from
	@S1.customer c
	,@S2.orderHist o
	,@S3.logFile l
	,@S4.profile p
where
	country='USA'
	and c.id = o.id
	and c.id = l.id
	and c.id = p.id

Practically this tool should do the following:

1) execute the linked queries

 a) run query "select * from customer"  from data source @S1 and save result as TEMP1
 b) run query "select * from orderHist"  from data source @S2 and save result as TEMP2
 c) run query "select * from logFile"  from data source @S3 and save result as TEMP3
 d) run query "select * from profile"  from data source @S4 and save result as TEMP4

2) save all temp tables into an intermediate storage

3) now run the query statement replacing all linked tables with actual TEMPxxx tables.