Reputation: 916
I'm attempting to insert records from a linked server using merge. The table only has two columns which both need to be used in order to check for new records.
MERGE dbo.[TableA] AS Target
USING (SELECT [PERMNUM], [ABS_DATE]
FROM [Linked Server].[dbo].[TableA])
AS source ([PERMNUM], [ABS_DATE]
ON (Target.[PERMNUM] = source.[PERMNUM]
AND Target.[ABS_DATE] = source.[ABS_DATE])
WHEN NOT MATCHED THEN
INSERT ([PERMNUM], [ABS_DATE])
VALUES (source.[PERMNUM], source.[ABS_DATE]);
I'm getting the following error.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'ON'.
How can I use both columns in the ON clause? I've looked at other examples and as far as I can tell I have it correct.
Upvotes: 8
Views: 22367
Reputation: 916
Well it looks like I had a missing ')'
I fixed the code so it is now correct:
SET NOCOUNT ON
MERGE dbo.[PSPI_DAY_ABSENCES] AS Target
USING (SELECT [PERMNUM], [ABS_DATE]
FROM [SQLPASS01].[PSPI].[dbo].[PSPI_DAY_ABSENCES])
AS source ([PERMNUM], [ABS_DATE])
ON Target.[PERMNUM] = source.[PERMNUM] AND Target.[ABS_DATE] = source.[ABS_DATE]
WHEN NOT MATCHED THEN
INSERT ([PERMNUM], [ABS_DATE])
VALUES (source.[PERMNUM], source.[ABS_DATE]);
Upvotes: 15