MERGE Statement Error

Question

UPDATE TO ORIGINAL QUESTION:

To give some insight on what I am doing. I have a table labeled [Staging].[HRIS_EEMaster] in this table is information on employees, there status with the company and so on. I am using their Personal ID numbers as the key [PersNo]. I have a column that we recently added that displays if the employees termination was Voluntary or Involuntary [Vol_Invol]. It seems that at some point, an HR rep was not careful and missed adding this value to 4 employees. I have attempted to go back to the source to correct this information but too much time has elapsed and I can not edit the data now.

What I want to do is update the [Vol_Invol] field for the 4 employees using the [PersNo] as the key. I need an UPDATE Script that will look at the [Staging].[HRIS_EEMaster], find the [PersNo] for the 4 employees and then update the blank [Vol_Invol] field with "Voluntary" (these 4 were all Voluntary Terms). Each employee has a unique [PersNo].

I will add this script to an existing SSIS Package. This SSIS package runs once a month. The staging table is truncated and then loaded with the new data. There is a second package with a SCD that feeds a master table that is used for reporting.

I want to add the UPDATE script to an SQL Task so that it updates (Corrects) these 4 blanks every time the data is pulled. I do something similar with the code below to correct missing Cost Centers that were removed in error.

I hope this helps, please let me know fi you need any more information.

ORIGINAL POST:

I am working on an HR Report and I have to correct 4 employees that are missing a Termination Status. I have used the following Statement before to correct issues with Termed Employees missing Cost Centers.

MERGE   [Staging].[HRIS_EEMaster] AS tgt
USING   (
    SELECT  PersNo AS EmpID,
        Vol_Invol   AS VIStat
    FROM  [dbo].[HRIS_MissingVol_Invol_Status]
    ) AS src ON src.EmpID = tgt.PersNo
WHEN    NOT MATCHED BY TARGET
    THEN    INSERT  (
                PersNo,
                Vol_Invol
            )
        VALUES  (
                src.EmpID,
                src.VIStat

            )
WHEN    MATCHED
    THEN    UPDATE
        SET tgt.PersNo =    CASE
                            WHEN src.VIStat > ''  THEN  src.VIStat
                            ELSE tgt.Vol_Invol 
                        END;

When I execute the script it returns an error:

Msg 245, Level 16, Stat 1, Line 1
Conversion failed when converting the nvarchar value 'Voluntary' to data type int.

I am not sure what it's talking about as I am simply trying to put the word "Voluntary" from a Table I created (see above script for Table Name) onto the Staging Table (Also above) and the column [Vol_Invol] is an nvarchar(50) in both tables.

Basically I have 4 people that are missing the required status (Historical information that can no longer be corrected at the source) and I want to run this MERGE Statement to replace the Blank that comes from the Monthly Export with the correct value.

This script is going to be used as an SQL Task in an already existing SSIS Process.

Answer 1

Forget MERGE - it is a hornet's nest of issues and I actually rewrite any customer code I come across (as do others). Why not just:

BEGIN TRANSACTION;

UPDATE trg
  SET trg.Vol_Invol = CASE 
    WHEN trg.PersNo = 12345 THEN 'Voluntary' 
    WHEN src.VIStat > '' THEN src.VIStat 
    ELSE trg.Vol_Invol END
FROM Staging.HRIS_EEMaster AS tgt
INNER JOIN dbo.HRIS_MissingVol_Invol_Status AS src
ON src.EmpID = tgt.PersNo;

INSERT Staging.HRIS_EEMaster(PersNo, Vol_Invol)
  SELECT src.EmpID, src.VIStat
  FROM dbo.HRIS_MissingVol_Invol_Status AS src
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM Staging.HRIS_EEMaster 
    WHERE PersNo = src.EmpID
  );

COMMIT TRANSACTION;

If the INSERT needs to account for the conditional, then change that portion to:

INSERT Staging.HRIS_EEMaster(PersNo, Vol_Invol)
  SELECT src.EmpID, 
    CASE WHEN src.EmpID = 12345 THEN 'Voluntary' ELSE src.VIStat END

But I am still completely confused by several aspects of your requirements - how you ended up with the MERGE statement you did, after reading your word problem, I have no idea...

Answer 2

I used the following script. It took a little playing around but I got it to work.

UPDATE [Staging].[HRIS_EEMaster]
SET [Vol_Invol] = CASE [PersNo]
    WHEN '2000602' THEN 'Voluntary'
    WHEN '2050004' THEN 'Voluntary'
    WHEN '2050234' THEN 'Voluntary'
    WHEN '2050746' THEN 'Voluntary'
ELSE [Vol_Invol]
END

Please let me know if anyone has a better script.

Answer 3

Looks like you're trying to set PersNo to VIStat...

SET tgt.PersNo =    CASE
                            WHEN src.VIStat > ''  THEN  src.VIStat