CODEWITHSUNDEEP
sql-servert-sqlmerge
user118190
user118190

Reputation: 2179

SQL INSERT with conditional statement IF THEN ELSE

I am trying to MERGE values from one table to another. One of the values is a conditional value, but it looks like I am not getting the syntax correctly. Initially, I was using an IF-THEN-ELSE statement but was advise to use a CASE statement instead.

Here is the gist the syntax that is failing:

CREATE PROCEDURE EmployeeMerge
AS 
BEGIN
    SET NOCOUNT ON;
    MERGE INTO Employee AS t1  
    USING 
        (SELECT 
               EmployeeName,
               Zip,
               UpdateDate 
        FROM table2) AS t2
        ON (t1.EmployeeID = t2.EmployeeID)
    WHEN MATCHED AND t2.UpdatedDate > t1.UpdatedDate THEN 
        UPDATE 
        SET 
            t1.EmployeeName = s.EmployeeName, 
            t1.Zip =
                (CASE 
                    WHEN t2.ZipExt IS NULL OR t2.ZipExt = '' THEN t2.Zip 
                    ELSE (t2.Zip + '-' + t2.ZipExt)
                END),
            t1.UpdatedDate = t2.UpdateDate
    WHEN NOT MATCHED THEN
        INSERT (EmployeeName,  
            Zip,  
            ModDate)
        VALUES 
            (t2.Name, 
            (CASE 
                WHEN t2.ZipExt IS NULL OR t2.ZipExt = '' THEN t2.Zip 
                ELSE (t2.Zip + '-' + t2.Zip_Ext)
            END),
            t2.UpdatedDate)
    OUTPUT 
        deleted.*, 
        $action, 
        inserted.* 
END; **-- A MERGE statement must be terminated by a semi-colon (;)**
GO

This MERGE statement works just fine if I do not implement the condition, i.e. simply set the t1.Zip = t2.Zip, but of course, this is avoiding the t2.ZipExt field.

Upvotes: 1

Views: 2105

Answers (2)

usr
usr

Reputation: 171178

A MERGE statement must be terminated by a semi-colon (;)

You haven't terminated the MERGE with a semicolon. You have terminated BEGIN-END. Move the semicolon.

Upvotes: 2

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

I never really cared for the merge command. There are times where I can see using it, but for the most part, it's more complicated than I like my SQL.

UPDATE e
SET     e.EmployeeName=t1.EmployeeName
    ,   e.Zip=CASE 
                WHEN t1.ZipExt IS NULL OR t1.ZipExt = '' THEN t1.Zip 
                ELSE (t1.Zip + '-' + t1.ZipExt)
            END
    ,   e.UpdatedDate=t1.UpdatedDate
FROM Employee e
INNER JOIN Table t1 ON e.EmployeeID = t1.EmployeeID
WHERE t1.UpdatedDate > e.UpdatedDate

INSERT INTO Employee (EmployeeName,Zip,UpdatedDate)
    SELECT
            t1.EmployeeName
        ,   t1.Zip=CASE 
                WHEN t1.ZipExt IS NULL OR t1.ZipExt = '' THEN t1.Zip 
                ELSE (t1.Zip + '-' + t1.ZipExt)
            END
        ,   t1.UpdatedDate
    FROM Table t1
    LEFT JOIN Employee e ON e.EmployeeID = t1.EmployeeID
    WHERE e.EmployeeID IS NULL

Upvotes: 0

Related Questions