Отношение "много ко многим" в SQL server-добавить ссылку во внешнюю таблицу, когда вставка не происходит в базовую таблицу

У меня есть две таблицы User и Item. Пользовательская таблица будет содержать сведения о пользователе, а таблица элементов будет содержать уникальные записи элемента.

Теперь я создаю отношение "многие ко многим", где есть отдельная таблица под названием List, которая ссылается на первичные ключи обеих таблиц как на внешний ключ. Теперь каждый пользователь будет передавать строку csv, которая будет содержать все элементы, которые он хочет. Эти элементы будут добавлены в таблицу элементов и его идентификатор вместе с идентификатором пользователя будет помещен в список стол.

Таблицы создаются следующим образом....

CREATE TABLE dbo.Item
(
Item_Id int identity(1,1) not null,
Item_Name varchar(30) not null,
PRIMARY KEY (Item_Id)
);

CREATE TABLE dbo.List
(
Item_Id int not null,
Users_Id varchar(11) not null,
);

Хранимая процедура выглядит примерно так...

ALTER procedure [dbo].[Entry] 
    @User_Id varchar(11)
    @Items VARCHAR(MAX)=NULL
as 
begin
    INSERT INTO dbo.Item
    OUTPUT INSERTED.Item_Id INTO @OutputTbl(Item_Id) 
       SELECT Item_Name
       FROM [dbo].[Split] ( @Items, ',')  -- call the split function 

    INSERT INTO dbo.List
       SELECT Item_Id, @User_Id 
       FROM @OutputTbl
end
GO
Теперь, когда есть дубликат имени Item, я хочу пропустить вставку в таблицу Item, но только запись должна быть помещена в таблицу List.

Например, если пользователь 1 дал список "мыло, шампунь" и он вставлен с идентификаторами 1 , 2, и рефренсы будут добавлены в таблицу списка. Теперь, когда User2 делает список "щетка, мыло" имя Soap не следует дублировать в таблице Item, но идентификатор soap должен быть добавлен в справочную таблицу, например

    UserID    |   ItemID
  ---------------------------
    User1         1 
    User1         2 
    User2         3 
    User2         1

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

3 2
sql-server stored-procedures foreign-key-relationship

3 ответа:

Я думаю, что вы, возможно, пытаетесь сделать слишком много сразу. У PROC, по-видимому, есть следующие 3 проблемы:

  1. разбиение списка имен элементов из строки ввода @Items через запятую
  2. сохранение таблицы dbo.Item с уникальным набором всех "видимых" имен элементов (т. е. добавление новых элементов в таблицу)
  3. сохранение dbo.List с отображениями User : ItemId независимо от того, является ли элемент "новым" или "существующим".

Я бы разделил заботы соответственно: 

CREATE PROCEDURE [dbo].[Entry] 
    @User_Id varchar(11),
    @Items VARCHAR(MAX)=NULL
AS BEGIN
    DECLARE @ItemNames TABLE (Name NVARCHAR(50));
    DECLARE @ItemIds TABLE (Item_Id INT);

    INSERT INTO @ItemNames(Name)
        SELECT Item_Name FROM [dbo].[Split] ( @Items, ',');

    INSERT INTO dbo.Item(Item_Id)
        SELECT Name
        FROM @ItemNames new
        WHERE NOT EXISTS(SELECT 1 FROM dbo.Item i WHERE i.Item_Id = new.Name);

    INSERT INTO dbo.List(itm.Item_Id, User_Id)
       SELECT Item_Id, @User_Id 
       FROM @ItemNames new
        INNER JOIN dbo.Item itm
        ON itm.Item_Id = new.Name;
END;

SqlFiddle здесь

2
ALTER procedure [dbo].[Entry] 
    @User_Id varchar(11)
    @Items VARCHAR(MAX)=NULL
as 
begin
    DECLARE @Items TABLE (Name VARCHAR(150));

    -- call the split function 
    INSERT INTO @ItemNames
       SELECT Item_Name
       FROM [dbo].[Split] ( @Items, ',');

    -- insert all new items into items
    INSERT INTO dbo.Item
    SELECT Item_Name
       FROM @Items
    except 
    select item_name from dbo.Item;

    -- insert user/item into list
    INSERT INTO dbo.List
    SELECT distinct Item_Id, @User_Id 
    from @Items iu
    join dbo.Item i on iu.item_name=i.item_name;
end
GO

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

2

Я отредактировал свой код, чтобы на самом деле хранить имена как temptable вместо ID, а затем я использовал внутреннее соединение, чтобы вставить Id в список из соответствующих имен в temptable.Хранимая процедура, которая работает для меня...

CREATE procedure [dbo].[Entry]
@Users_Id varchar(11),
@Items VARCHAR(MAX)=NULL
as 
begin
DECLARE @ItemNames TABLE (Item_Names VARCHAR(150));

INSERT INTO @ItemNames 
SELECT Item_Name  
FROM [dbo].[Split] ( @Items, ',')  -- call the split function 

INSERT INTO dbo.Item
SELECT Item_Names
FROM @ItemNames list
WHERE NOT EXISTS(SELECT 1 FROM dbo.Item i WHERE i.Item_Name = list.Item_Names);

INSERT INTO dbo.List(Item_Id,Users_Id)
SELECT  Item_Id,@Users_Id 
FROM @ItemNames list
INNER JOIN dbo.Item 
    ON list.Item_Names = dbo.Item.Item_Name;
end
GO
1