在SQL中创建MERGE [英] Create a MERGE in SQL

问题描述

我早上一直在使用Server 2008 R2服务器上的MERGE语句工作。



我遇到的问题是它失败并出现非常一般的错误提取了数百条类似的错误消息，这些消息并没有让我更接近解决问题。



我创建了一个基本上插入所有数据的合并已经有记录的另一个表。



原始表名为Stock，要更新的表名为xStock。



基本上如果主键已存在于xStock中，我只想更新字段以防止重复，如果主键不存在，我想将整个角色作为新记录插入。



我这样做的原因是将它作为存储过程的一部分添加，并作为单个语句执行插入和更新，以防止一个接一个地执行问题。



然而，



我是ep得到这条消息，我完全不明白为什么：

I have been working all morning on a MERGE statement on a Server 2008 R2 Server.

The problem I have is that it fails with a very generic error which pulls up hundreds of similar error messages which aren't getting me closer to solving the issue.

I have create a merge that is going to basically insert all data from one table into another which already has records in.

The original table is called Stock and the table to be updated is called xStock.

Basically if the Primary Key already exists in xStock I just want to update fields to prevent duplication, and if the Primary Key doesn't I want to insert the whole role as a new record.

The reason I am doing it this way is to add it as part of stored procedure and to perform the insert and update as a single statement to prevent issues of doing one after the other.

However,

I keep getting this message and I have absolutely no idea why:

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ' '.

有没有人有Merge的经验或知道为什么会这样？



任何帮助非常感谢，我花了2个小时谷歌搜索所有东西，但无法随身携带！



谢谢



以下代码：

Does anyone have experience with Merge or know why this could be happening?

Any assistance would be greatly appreciated, I have spent 2 hours googling everything but can't get anywhere with it!

Thanks

Code Below:

Use MyDb
Merge xStock WITH (HOLDLOCK) as Xst
Using Stock
On Xst.StockCodeIndex = Stock.StockCodeIndex

when MATCHED then

Update Set Xst.Desc1 = Stock.Desc1, Xst.Desc2 = Stock.Desc2, Xst.Desc3 = Stock.Desc3, Xst.Desc4 = Stock.Desc4

when NOT MATCHED then

Insert (StockCodeIndex, Desc1, Desc2, Desc3, Desc4)

Values (Stock.StockCodeIndex, Stock.Desc1, Stock.Desc2, Stock.Desc3, Stock.Desc4);

推荐答案

我最好的猜测是：添加额外的[; ]在行尾使用MyDb 。

My best guess is: add extra [;] at the end of line Use MyDb.

这篇关于在SQL中创建MERGE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！