带有Insert子查询的SQL Server MERGE

I'm having trouble getting this compound insert to work in my MERGE statement between two tables (Ignore the when match condition, I know its bad practice). The issue I'm having is getting the ServerId field in the target table to fill. The Team field is filling fine but all of the rows have a null value for ServerId. I can't find an example online for this so I'm hoping someone can help. I don't seem to have any syntactical errors and I know the column 'ServerName' in the Source table is filled for all rows.

我无法在两个表之间的MERGE语句中使用此复合插入(忽略匹配条件,我知道它的不良做法)。我遇到的问题是获取目标表中的ServerId字段来填充。 Team字段填写正常但所有行都具有ServerId的空值。我在网上找不到这样的例子,所以我希望有人可以提供帮助。我似乎没有任何语法错误,我知道Source表中的'ServerName'列填充了所有行。

    MERGE ApplicationTeams AS Target
    USING TempApplicationTeams AS Source
    ON (Target.ServerId = (SELECT ID from Servers WHERE Name='Source.ServerName') AND Target.Team = Source.Team)
    WHEN MATCHED THEN
    UPDATE SET Target.Team = Target.Team
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (ServerId, Team) VALUES((SELECT ID from Servers WHERE Name='Source.ServerName'), Source.Team)
    WHEN NOT MATCHED BY SOURCE THEN
    DELETE
    ;

Thanks.

1 个解决方案

#1

I think you should remove the single quoutes on the where clausule.

我认为你应该删除where clausule上的单个quoutes。

You wrote: (SELECT ID from Servers WHERE Name='Source.ServerName')

您写道:(来自Servers WHERE Name ='Source.ServerName'的SELECT ID)

But I think you should write this: (SELECT ID from Servers WHERE Name=Source.ServerName)

但我认为你应该这样写:(来自Servers WHERE Name = Source.ServerName的SELECT ID)

And make sure the select id returns only one row otherwise the statement will fail

并确保select id只返回一行,否则语句将失败

I hope it will be usefully

我希望它会有用

#1