主页 / dba / 问题 / 219026
Accepted
Himilou
Asked: 2018-10-02 14:05:02 +0800 CST

尝试仅将唯一项插入一个表，然后仅当该连接不存在时才在连接表中创建多对多关系

  • 772

可能最好用一个例子来解释这一点。

我有 3 个表，其中两个是“常规”表，第三个是连接前两个的多对多关系表。

我有一个创建临时表的存储过程，然后仅当它们不存在时才将临时表中的值插入到现有表 B 中，然后在表 B 和另一个常规表之间创建多对多关系。这工作正常，直到我命中一个之前插入到 B 中的值并且在多对多表中也有一个现有关系，然后我得到一个约束冲突。我遇到问题的地方是试图让插入到多对多连接表中，只有在没有现有条目时才会发生。

CREATE TABLE A
    (ID INT PRIMARY KEY,
     Number INT)

CREATE TABLE B
    (ID INT IDENTITY(1,1) PRIMARY KEY,
     Name nvarchar(100))


CREATE TABLE AtoB(
[AId] [int] NOT NULL,
[BId] [int] NOT NULL,
 CONSTRAINT [AtoB_PK] PRIMARY KEY 
(
[AId] ASC,
[BId] ASC
),
CONSTRAINT FKtoA FOREIGN KEY (AId) REFERENCES A(ID),
CONSTRAINT FKtoB FOREIGN KEY (BId) REFERENCES B(ID),  
)

一些初始值

insert into A ( ID, Number) Values (1,1000),(2,1001),(3,1234);

工作（第一次）插入

DECLARE @temptable TABLE (firstname varchar(200))
DECLARE @tableAid INT
DECLARE @tableBid INT

SET @tableAid = (SELECT ID FROM A WHERE  A.Number = 1001)

INSERT INTO @temptable (firstname) VALUES ('John'),('James'),('Bill')

INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS 
( SELECT 1 FROM B WHERE B.Name = TT.firstname )

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable 
T ON B.Name = T.firstname

如果您再次运行此语句但使用一些不同的值，则此语句的最后部分将失败（如预期的那样）

INSERT INTO @temptable (firstname) VALUES ('John'),('Ted'),('Alice')

我想要发生的是，在第二次运行中，Ted 和 Alice 将插入多对多关系。我试过了

INSERT INTO AtoB (AId, BId ) SELECT B.Id,  @tableAid FROM B JOIN @temptable 
T ON B.Name = T.firstname WHERE NOT EXISTS 
SELECT ( * FROM AtoB JOIN B ON AtoB.BId = B.Id JOIN @temptable ON B.Name = 
@temptable.firstname WHERE AtoB.Aid = @tableAid And AtoB.BId = B.Id

和几个变体，但我通常最终没有将任何值插入到多对多表中。

关于错误的想法？

谢谢

长回复下面的答案这意味着只有在不存在任何项目时才需要插入。显然，这不是你想要的”——这正是我想要的。在表 BI 中，每个名称都需要一个条目。这部分存储过程可以正常工作
INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS（SELECT 1 FROM B WHERE B.Name = TT.firstname）

当我到达我想要创建多对多关系的下一部分时，问题就开始了，但前提是它是表 B 中的新名称值。如果传入的值填充 temptable 包含一个项目，该项目以前在 A 之间关联和 B 语句被终止，新的（和以前未插入到 B 中的）值不会被输入。

sql-server many-to-many

3 个回答

  1. Best Answer

    您可以NOT EXISTS将第一个插入（或LEFT JOIN / IS NULL）用作第二个插入，使其像第一个一样工作，仅在该行不存在时插入。另一种方法是EXCEPT：

    INSERT INTO B (Name) 
SELECT firstname FROM @temptable
EXCEPT 
SELECT Name FROM B ;


INSERT INTO AtoB (AId, BId) 
SELECT @tableAid, B.Id FROM B JOIN @temptable T ON B.Name = T.firstname 
EXCEPT
SELECT Aid, Bid FROM AtoB ;
    • 2

  2. 问题出在 WHERE NOT EXISTS 部分。
    这意味着只有在不存在任何项目时才需要插入。
    显然，这不是你想要的。

    那么你需要做什么？

    INSERT INTO AtoB (AId, BId ) 
SELECT B.Id,  @tableAid 
FROM B JOIN @temptable 
T ON B.Name = T.firstname 
LEFT JOIN ( 
SELECT ( ---insert specific columns in here

 FROM AtoB JOIN B ON AtoB.BId = B.Id JOIN @temptable ON B.Name = 
@temptable.firstname WHERE AtoB.Aid = @tableAid And AtoB.BId = B.Id) Q
ON ---What's your join?
WHERE Q.id is null

    基本上我在说什么（这只是一个代码草案，但顺便说一句，你已经表达了你的问题，你可以立即修复它）：

    1. 确保您选择了所需的特定列
    2. 带有 LEFT JOIN 的 INSERT 仅缺失值。我总是运行带有 LEFT JOIN 的 SELECT 语句来检查我是否首先插入了正确的值。

    3. 另一个问题：您在表 A 和 B 上的 KEY 基于 ID，但您确实需要检查您是否也没有在表 A 和 B 中输入重复值，

      INSERT INTO B (Name) SELECT TT.firstname FROM @temptable TT WHERE NOT EXISTS ( SELECT 1 FROM B WHERE B.Name = TT.firstname )

    应该变成：

    INSERT INTO B (Name) 
SELECT TT.firstname 
FROM @temptable TT LEFT JOIN B ON 
 (B.Name = TT.firstname )
WHERE B.id IS NULL
    • 0

  3. 实际上，我刚刚使用第二个临时表找到了解决此问题的方法。我只存储 B 表中不存在的值，然后使用第二个表进行插入，以便只处理不存在条目的值。

    DECLARE @temptable TABLE (firstname varchar(200))
DECLARE @nodupetable TABLE (fname varchar(200))
DECLARE @tableAid INT


SET @tableAid = (SELECT ID FROM A WHERE  A.Number = 1001)

INSERT INTO @temptable (firstname) VALUES ('John'),('James'),('Bill')
--,('Tom'),('Harris'),('Will')    -- Uncomment this line after one execution 
to test the many to many table insert

INSERT INTO @nodupetable (fname) SELECT TT.firstname FROM @temptable TT 
WHERE NOT EXISTS ( SELECT 1 FROM B WHERE B.Name = TT.firstname )

INSERT INTO B (Name) SELECT TT.fname FROM @nodupetable TT WHERE NOT EXISTS ( 
SELECT 1 FROM B WHERE B.Name = TT.fname )

INSERT INTO AtoB (AId, BId ) SELECT @tableAid, B.Id  FROM B JOIN 
@nodupetable T ON B.Name = T.fname

    我想我也可以在 AtoB 表上使用左联接，然后检查空值以查找没有行的项目，但这种方法对我来说似乎更清楚。感谢您的输入！

    • 0

相关问题