Заполнить внешние ключи для таблицы подтипов, уже содержащей данные

У меня есть таблицы [Формы], [Машины] и [Запчасти], каждая с разными атрибутами/столбцами. Я хотел бы сделать их подтипами и создать для них таблицу супертипов под названием [Активы], чтобы я мог ссылаться на них все вместе в приложении для планирования обслуживания.

Таблица [Активы] будет просто содержать столбцы [Актив_ID], [Актив_Тип] и [Описание]. [Asset_ID] — это идентификатор PK, [Asset_Type] — это int (например, Molds = 1, Machines = 2 и т. д.), а [Description] будет взято из таблиц подтипов. Я добавлю столбец с именем [Asset_FK] в каждую из таблиц подтипов в качестве внешнего ключа.

Моя проблема в том, что в каждой таблице подтипов уже есть от сотен до тысяч строк данных. Было бы неразумно вручную создавать PK-FK для каждой существующей записи, но я не уверен в SQL, необходимом для его автоматизации.

Для заполнения таблицы [Активы] в настоящее время у меня есть это:

DECLARE @AssetID TABLE (ID int)
INSERT INTO Assets (Assets.Description, Assets.Asset_Type)
OUTPUT Inserted.Asset_ID INTO @AssetID
SELECT IsNull(Moulds.Description,''), 5
FROM Moulds

Но я не уверен, как обновить FK в [Moulds] в том же запросе, и является ли это правильным подходом. В частности, я не уверен, как определить строку в выбранных подтипах, которые я хочу обновить.

Подводя итог моему вопросу, у меня есть пустая таблица супертипов и заполненные таблицы подтипов. Я хочу заполнить таблицу супертипов, используя таблицы подтипов, и автоматически заполнить значения FK для существующих записей подтипов, чтобы связать их. Как это сделать с помощью SQL (MS SQL Server 2008r2)?


tsql sql-server-2008 foreign-keys normalization supertype
person NeutronFlux    schedule 01.03.2013    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Попробуй это:

update m 
set m.fkid = a.id 
from moulds m 
inner join assets a 
   on isnull(m.description,'') = a.description and a.Asset_Type = 5
inner join @AssetID a2 on a.id = a2.id
person rs.    schedule 01.03.2013
comment
Спасибо за ответ. К сожалению, Description не всегда уникален, так что это не совсем подходит для моего случая. На самом деле, это наводит меня на мысль. - person NeutronFlux; 01.03.2013

arrow_upward
0
arrow_downward

Итак, основываясь на ответе RS, у меня возникла идея. Я добавляю временный столбец в таблицу [Активы], в котором хранится PK таблицы [Формы] (или таблицы другого подтипа), использую его для операций обновления, затем удаляю столбец. Это выглядит так:

USE [Maintenance]

ALTER TABLE Assets
ADD Asset_FK int null

GO

DECLARE @AssetID TABLE (ID int)

INSERT INTO Assets (Description, Asset_Type, Asset_FK)
OUTPUT Inserted.Asset_ID INTO @AssetID
SELECT IsNull(Description,''), 5, Mould_PK
FROM Moulds

UPDATE m 
SET m.Asset_ID = a.Asset_ID
FROM Moulds m 
INNER JOIN Assets a 
   ON m.Mould_PK = a.Asset_FK AND a.Asset_Type = 5
INNER JOIN @AssetID a2 ON a.Asset_ID = a2.ID

GO

ALTER TABLE Assets
DROP COLUMN Asset_FK

Вероятно, не самый элегантный ответ, но он кажется простым и работает.

person NeutronFlux    schedule 01.03.2013