merge statement for update and insert

Question

I am trying to use merge to combine an update and insert statement, although i am getting some error and im not quite sure its right. The merge statement is within a stored proc. Following is the merge statement.

MERGE dbo.seg AS TARGET
USING (SELECT segCreateDate
        , segDesc
        , modifiedDate
        , modifiedBy 
    FROM [update].[dbo].[seg] s
    ) AS source (segCreateDate
                , segDesc
                , modifiedDate
                , modifiedBy)
        ON  (dbo.[seg].segID = s.segID
            AND (
                 dbo.[seg].segCreateDate > s.segCreateDate
                 OR dbo.[seg].segDesc <> s.segDesc
                )
    )

WHEN MATCHED THEN
UPDATE dbo.seg SET
  target.segCreateDate = source.segCreateDate
  AND target.segDesc = source.segDesc
  AND target.modifiedDate = source.modifiedDate
  AND target.modifiedBy = source.modifiedBy

WHEN NOT MATCHED THEN
INSERT (segID
        , segCode
        , segDesc
        , segCreateDate
        , createdDate
        , createdBy
        , modifiedDate
        , modifiedBy
        )
VALUES (SELECT  segID
                ,segCode
                ,segDesc
                ,segCreateDate
                ,createdDate
                ,createdBy
                ,modifiedDate
                ,modifiedBy
         FROM [update].[dbo].[seg]);

Its the first time i am using merge so hope can get some help.

Thanks all

at the update, i have error there saying 'incorrect syntax near dbo' @Radu — Faiz, Oct 06 '15 at 14:22
@Randy my question is i dont think what i wrote is working is working. is the logic i am using correct? — Faiz, Oct 06 '15 at 14:23
ya i saw that here just a few mins ago was about to edit it. its actually _update.dbo.seg and sql is happy with that. in the update statement the dbo is underlined red and in the values, select and the final arrow is underlined red too. im real confused about this one — Faiz, Oct 06 '15 at 14:55

score 0 · Accepted Answer · answered Oct 07 '15 at 09:49

There were several problems with your merge statement. This is what you probably wanted.

merge dbo.seg as TARGET
using
    (
     select segCreateDate,
            segDesc,
            modifiedDate,
            modifiedBy
     from   [update].dbo.seg s
    ) as source (segCreateDate, segDesc, modifiedDate, modifiedBy)
on (
    TARGET.segID = source.segID
    and (
         TARGET.segCreateDate > source.segCreateDate
         or TARGET.segDesc <> source.segDesc
        )
   )
when matched then
    update set TARGET.segCreateDate = source.segCreateDate,
               TARGET.segDesc = source.segDesc,
               TARGET.modifiedDate = source.modifiedDate,
               TARGET.modifiedBy = source.modifiedBy
when not matched then
    insert (
            segID,
            segCode,
            segDesc,
            segCreateDate,
            createdDate,
            createdBy,
            modifiedDate,
            modifiedBy
           )
    values (
            source.segID,
            source.segCode,
            source.segDesc,
            source.segCreateDate,
            source.createdDate,
            source.createdBy,
            source.modifiedDate,
            source.modifiedBy
           );

Note that (1) when updating a table you don't write and after each column but merely add a comma. (2) If you are using Source and Target as the table alias then you should stick to them and don't change in the middle to s instead of source.

Still the above code will probably not work because (at the end of the code) it is trying to insert into the target table more columns than there are in the source table. At first you are saying that the source table consists of segCreateDate, segDesc, modifiedDate, and modifiedBy. But at the end you are also trying to insert into target the columns segID, segCode, createdDate, and createdBy.

This leads me to the second problem why the above code cannot work: you are trying to join source and target on segID. I cannot tell if this column exists in the target table dbo.seg. But this column is certainly not contained in the source table as defined above.

merge statement for update and insert

1 Answers1