0

For example if I have DB_1 and table in it Table_1 and several columns in that table including Column_5. Then there is a second database locally DB_2 with Table_6 and in it Column_6.

Is there a way to create some sort of link between DB_1.Table_1.Column_5 and DB_2.Table_6.Column_6 so when selects or inserts are done in DB_1.Table_1.Column_5 it knows to get or set that data in DB_2.Table_6.Column_6?

I know I can sync the two together and/or use triggers but

  1. We don't want to take the space in DB_1 (to store same data at two places) and
  2. Don't want to modify the higher level application that sits on top of DB_1 to change the queries to use a column from a linked server.

At this point there is no application that sits on top of DB_2.

John Watt
  • 29
  • 2

1 Answers1

0

When you do selects, you can do cross-database joins:

select ...
from [db1].[dbo].[table1]
join [db2].[dbo].[table2]
on ...

Read more here: https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/cross-database-queries?view=sql-server-ver15

As about entangling the values, you could create a trigger on [db1].[table1] which will update [db2][table2].

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Thanks. As I mentioned we don't want to store a copy on db1.table1 but only a link. The idea would be that you would just do a select * from [db1].[dbo].[table1] and it will know to get the column5 field from db2 by only storing some sort of link there. I guess that isn't possible? – John Watt Jul 21 '21 at 20:29
  • @JohnWatt It's possible, see my answer. It does not involve copying the table from one database to the other. – Lajos Arpad Jul 21 '21 at 20:31
  • @JohnWatt this approach leaves all tables where they are and does cross-database queries and triggers. – Lajos Arpad Jul 21 '21 at 20:48
  • 2
    You can also create a view that accesses multiple databases: https://stackoverflow.com/questions/2143199/tsql-create-a-view-that-accesses-multiple-databases – Lajos Arpad Jul 21 '21 at 20:49
  • How would the system know what row matches? If all of the values are the same in all rows in both databases - but you no longer want to use the table in DB1 you can rename that table and create a synonym that points to DB2. – Jeff Jul 21 '21 at 21:35
  • @Jeff It depends. There might be some pattern which can be used for the matching. For example, invoices can be identified by (year,month,day,invoice_number). If there are invoices in both databases, then you can join them by matching these fields. – Lajos Arpad Jul 21 '21 at 21:58