Выбор данных, которые могут находиться во многих базах данных

Это настолько сложно (по крайней мере, в моей голове), что я не мог правильно назвать свой вопрос.

У меня есть сотни баз данных (по одной на каждую Supplier), к которым мне нужно подключиться из центральной базы данных, содержащей таблицы Order, Customer и Supplier.

В основном таблица Suppplier в центральной базе данных выглядит так:

SupplierID | SupplierName | SupplierDatabaseName
1            Nestle         Nestle005
2            Mars           Mars001

Он содержит подробную информацию о поставщике и, самое главное, о том, как называется его имя базы данных.

Когда клиент размещает заказ, он попадает в таблицу заказов центральной базы данных следующим образом:

OrderID | CustomerID | SuppplierID | ProductID | Qty
1         1            2             100         5

В базе данных поставщика, такой как Mars001, есть таблица, в которой эта компания может управлять информацией о своих продуктах, например:

ProductID | ProductName
100         Skittles

Что мне нужно сделать, так это вернуть список заказов клиента вместе с ProductName. Это означает, что я должен:

  1. Выберите все из таблицы Order центральной базы данных
  2. Каким-то образом динамически укажите имя базы данных поставщика, которое будет использоваться в инструкции JOIN, чтобы получить ProductName из таблицы продуктов в этой базе данных.

Окончательный вывод должен выглядеть так:

OrderID | CustomerID | SupplierID | ProductID | ProductName
1         1            2            100         Skittles

Я в некоторой степени понимаю динамический sql и могу динамически указывать имя базы данных, используя параметр. Но это работает только тогда, когда значение параметра остается неизменным в запросе. Это сложнее, чем это.

Может ли кто-нибудь предложить способ решения этой проблемы? Возможно, это невозможно сделать в одном запросе?


sql sql-server sql-server-2012 sql-server-2014
person volume one    schedule 17.05.2015    source источник
comment
Это случай очень плохого дизайна базы данных. Скажем, у вас есть продукт А, поставляемый 100 поставщиками, вам нужно создать 100 подключений из вашего приложения к каждой базе данных и выполнить запрос к ней. Все ваши базы данных находятся на одном сервере? Если это так, вы можете написать хранимую процедуру, которая может запрашивать все базы данных, но думать о производительности. Другим вариантом является наличие какого-либо запланированного задания, которое может синхронизировать данные из всех баз данных поставщиков с вашей центральной базой данных и запрашивать только вашу центральную базу данных.   -  person Hakunamatata    schedule 17.05.2015
comment
комбинация ProductID и SupplierID делает его уникальным. Таким образом, ProductID 100 может быть холодильником, если он взят из SupplierID 32. По сути, у каждого поставщика есть собственная база данных, в которой можно управлять своими продуктами. Но все заказы хранятся централизованно   -  person volume one    schedule 17.05.2015

Ответы (1)


arrow_upward
3
arrow_downward

Хотя этот тип разбиения может быть не самым лучшим, возможно, вы застряли с ним. Однако базовый динамический SQL для выполнения того, что вы хотите, не так уж и сложен:

declare @sql nvarchar(max) 
select @sql = N'
    select o.orderid, o.customerid, s.supplierid, p.productid, p.productname 
    from [order] o 
    join Supplier s on o.SuppplierID = s.SupplierID 
    join ' + s.Supplierdatabasename + N'..product p on o.ProductID = p.productid'
from [order] o join Supplier s on o.SuppplierID = s.SupplierID

exec(@sql)

Это даст вам результат, например:

orderid     customerid  supplierid  productid   productname
----------- ----------- ----------- ----------- --------------------
1           1           2           100         Skittles

Запрос, безусловно, можно улучшить, но он должен дать вам представление о том, как действовать дальше.

person jpw    schedule 17.05.2015
comment
У меня есть возможность изменить дизайн БД, если это необходимо. однако это казалось хорошей идеей, потому что это означало, что каждая компания могла управлять своей продукцией независимо друг от друга. - person volume one; 17.05.2015
comment
Что ж, это прекрасно сработало. Как раз то, что мне было нужно, спасибо jpw - person volume one; 17.05.2015