SQL Server 2014 - 合并 - 语法错误

Question

我有一个查询，其中合并语句作为在SQL Server中使用foreachdb的较大脚本的一部分运行。我不断收到随机语法错误 -

Msg 156, Level 15, State 1, Line 59
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 72
Incorrect syntax near ')'.

我的脚本有什么问题？当合并在foreachdb之外运行时，它运行良好，没有任何错误。在foreachdb内部时，它每次都会失败并出现不同的错误。

USE RedshiftDatabase;

EXEC sp_MSforeachdb
'
BEGIN
USE ?;
TRUNCATE TABLE #UnMatchedTransactions

PRINT(''truncate complete'');

INSERT INTO #UnMatchedTransactions
SELECT
DB_NAME(),
TxnID,
BatchID,
DateCreated,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
0
FROM
UnMatchedTransactions;
SELECT db_name(),''done'';
SELECT ClientName, COUNT(1) FROM #UnMatchedTransactions GROUP BY ClientName;
CHECKPOINT;
BEGIN TRANSACTION merge_tran

MERGE INTO RedshiftDatabase.dbo.UnMatchedTransactions AS TARGET
USING  #UnMatchedTransactions AS SOURCE
ON  
(
TARGET.ClientName = SOURCE.ClientName 
AND 
TARGET.TxnID = SOURCE.TxnID
AND 
TARGET.BatchID = SOURCE.BatchID
)
WHEN MATCHED AND (TARGET.DateCreated <> SOURCE.DateCreated)
THEN
UPDATE SET 
DateCreated  = SOURCE.DateCreated, 
UpdatedTS = CURRENT_TIMESTAMP
WHEN NOT MATCHED BY TARGET
THEN
INSERT ( ClientName,
TxnID,
BatchID,
DateCreated
) 
VALUES (SOURCE.ClientName,
SOURCE.TxnID,
SOURCE.BatchID,
SOURCE.DateCreated
)
WHEN NOT MATCHED BY SOURCE 
THEN 
UPDATE SET 
TARGET.IsDeleted = 1,
TARGET.UpdatedTS = CURRENT_TIMESTAMP
;
END TRANSACTION merge_tran;
CHECKPOINT;
SELECT db_name(),''Mergedone'';
SELECT BL_ClientName, COUNT(1) FROM RedshiftDatabase.dbo.UnMatchedTransactions GROUP BY ClientName;
END;
END
'

Answer 1

以下是对代码的一些观察。

为什么要使用sp_MSforeachdb？您可以使用 CTE 通过系统表列出服务器上的所有数据库名称，然后您可以编写动态 sql 来迭代合并语句的列表？

这是针对您的问题的建议解决方案。

BEGIN
USE ?;
-- TO DO Check if table exists
TRUNCATE TABLE #UnMatchedTransactions
PRINT('truncate complete');
-- To DO create #UnMatchedTransactions each time as database name changed so scope changed and table doesn't exist in new scope. Create table in each iteratation
-- Second option use ##UnMatchedTransactions and delete row per database at the end
INSERT INTO #UnMatchedTransactions
SELECT
DB_NAME(),
TxnID,
BatchID,
DateCreated,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
0
FROM
UnMatchedTransactions;
SELECT db_name(),'done';
SELECT ClientName, COUNT(1) FROM #UnMatchedTransactions GROUP BY ClientName;
CHECKPOINT;
BEGIN TRANSACTION merge_tran
MERGE INTO RedshiftDatabase.dbo.UnMatchedTransactions AS TARGET
USING  #UnMatchedTransactions AS SOURCE
ON  
(
TARGET.ClientName = SOURCE.ClientName 
AND 
TARGET.TxnID = SOURCE.TxnID
AND 
TARGET.BatchID = SOURCE.BatchID
)
WHEN MATCHED AND (TARGET.DateCreated <> SOURCE.DateCreated)
THEN
UPDATE SET 
DateCreated  = SOURCE.DateCreated, 
UpdatedTS = CURRENT_TIMESTAMP
WHEN NOT MATCHED BY TARGET
THEN
INSERT ( ClientName,
TxnID,
BatchID,
DateCreated
) 
VALUES (SOURCE.ClientName,
SOURCE.TxnID,
SOURCE.BatchID,
SOURCE.DateCreated
)
WHEN NOT MATCHED BY SOURCE 
THEN 
UPDATE SET 
TARGET.IsDeleted = 1,
TARGET.UpdatedTS = CURRENT_TIMESTAMP
;
END TRANSACTION merge_tran;
CHECKPOINT;
SELECT db_name(),'Mergedone';
SELECT BL_ClientName, COUNT(1) FROM RedshiftDatabase.dbo.UnMatchedTransactions GROUP BY ClientName;
END;
-- if using Second option then drop here so that temp tables cleaning would be done for each databaseuse
END