1

I have two different databases in SQL Server, lets say db1 and db2, and I need to join data between them, but using my application that has access only to db1.

So, I thought of two possibilities: the first one is to create a view in db 1 that reads data from db 2. The second one is to create some kind of logical or virtual database, kind of db3, so I can access db1 and db2 through db3. Is it possible?

Opened for other suggestions.

dsolimano
  • 8,870
  • 3
  • 48
  • 63
Julio Furquim
  • 85
  • 6

3 Answers3

1

The view is a good option, I don't see the need for a "virtual" db. Use the KISS principle.

Icarus
  • 63,293
  • 14
  • 100
  • 115
  • I like the view option. My only concern is that the database name in the view to access db2 need to be fixed. As I work for a SW house, database names changes between some customers and in this case we're forced to manualy change the views. Unless there's a trick I don't know! – Julio Furquim May 04 '12 at 13:02
0

"logical or virtual database"? no need. Assuming the DBs are on the same server, a view or even a direct select like select * from db2.dbo.table would do the trick

If they are not, you would need to create a linked server on DB1 pointing to DB2

Diego
  • 34,802
  • 21
  • 91
  • 134
0

Yes, create a stored procedure and do a "union/join" SQL query.

In the SQL Query in the "from" clause you need to write the "from [DatabaseName].[Schema].[Table]"

You need to host the stored procedure in the database where your application has access to.

Internet Engineer
  • 2,514
  • 8
  • 41
  • 54