Merge on multiple columns

sql, sql-server, t-sql

Solution

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]);

Problem

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.

Original source