Joins across databases?
- Thread starter AuntPeggy
- Start date
TangoWhiskey
Touchdown! Greaser!
Yes, from SQL Server. Look up "Linked Servers". You can link to SQLite from the SQL by creating an ODBC connection as a Linked Server.
http://doc.ddart.net/mssql/sql70/1_server_10.htm
http://doc.ddart.net/mssql/sql70/1_server_10.htm
gprellwitz
Touchdown! Greaser!
- Joined
- Jun 19, 2005
- Messages
- 12,765
- Location
- Romeoville, IL
- Display Name
Display name:
Grant Prellwitz
I doubt it would be the most efficient join ever, but I don't know where performance is on your criteria. If it's a complex join that will be run frequently against largely static data, it might be more efficient to periodically clone some tables to a common database that can have all the requisite metadata to optimize the query. But it looks like a nice, quick, easy way to do it quickly. Sounds somewhat similar to database links in Oracle.
ReverendSlappy
Ejection Handle Pulled
Yeah, generally, using linked servers is a "bad" idea precisely because of performance considerations. But it sure is a lot easier than doing some kind of SSIS/DTS work to get the data across. So if an ETL kind of thing isn't doable but performance is still a concern, the best bet might be to grab the data from the remote server using OPENQUERY and put it in a temp table or table variable (maybe apply indexes as and if necessary?), then join the local data against that.
Greebo
N9017H - C172M (1976)
- Joined
- Feb 11, 2005
- Messages
- 10,976
- Location
- Baltimore, MD
- Display Name
Display name:
Retired Evil Overlord
Like the others have said - it all depends on the specifics of the situation. For example if you need to pull data from SQL into SQLLite, then you may very well need to set up a replication scheme using linking from SQL into SQLLite.