Help syntax for Stored Procedure to INsert/Update records from 1 table into another



  • I am trying to create a stored procedure to insert or update records from 1 table into another table and I am having several syntax issues.

    1. it does not like the @MergeLog declaration

    2. it errors on the commas from the Update statement

    CREATE PROCEDURE [dbo].[sp_tblUsers_Dim_InsertUpdate]
     @MergeLog TABLE 
    AS
    BEGIN
    

    MERGE [dbo].[vwUserStaging_DIM] T
    USING [dbo].[tblUsers_DIM] S
    ON s.[UserID] = T.[User_ID]
    WHEN MATCHED
    THEN UPDATE
    SET t.[User_RecID]
    , t.[User_ID]
    , t.[FirstName]
    , t.[LastName]
    , t.[FullName]
    , t.[EMail]
    , t.[UserRoles]
    , t.[PostionType]
    , t.[ManagerID]
    , t.[UUID]
    , t.[External_UUID]
    , t.[home_Location_id]
    , t.[Home_Location_Name]
    , t.[Home_Organization_ID]
    , t.[Home_Organization_Name]
    , t.[Record_types]
    , t.[Location_Ceiling_ID]
    , t.[Location_Ceiling_Name]
    , t.[Organization_Ceiling_ID]
    , t.[Payroll_Identifier]
    , t.[Created_Date]
    , t.[Update_Date]
    , t.[Audit_Date]
    WHEN NOT MATCHED BY TARGET
    THEN INSERT ([User_ID]
    , t.[FirstName]
    , t.[LastName]
    , t.[FullName]
    , t.[EMail]
    , t.[UserRoles]
    , t.[PostionType]
    , t.[ManagerID]
    , t.[UUID]
    , t.[External_UUID]
    , t.[home_Location_id]
    , t.[Home_Location_Name]
    , t.[Home_Organization_ID]
    , t.[Home_Organization_Name]
    , t.[Record_types]
    , t.[Location_Ceiling_ID]
    , t.[Location_Ceiling_Name]
    , t.[Organization_Ceiling_ID]
    , t.[OrganizationCeilingName]
    , t.[Payroll_Identifier]
    , t.[Created_Date]
    , t.[Update_Date]
    , t.[Audit_Date])
    VALUES (
    s.[UserID]
    , s.[first_name]
    , s.[last_name]
    , s.[full_name]
    , s.[email]
    , s.[role_id]
    , s.[position]
    , s.[manager_id]
    , s.[uuid]
    , s.[external_uuid]
    , s.[home_location_id]
    , s.[LocationName]
    , s.[home_organization_id]
    , s.[OrganizationName]
    , s.[type]
    , s.[location_ceiling_id]
    , s.[LocationCeilingName]
    , s.[organization_ceiling_id]
    , s.[OrganizationCeilingName]
    , s.[payroll_identifier]
    , s.[CreateDate]
    , s.[UpdateDate]
    , s.[Audit_Date])
    WHEN NOT MATCHED BY SOURCE
    THEN DELETE
    OUTPUT s.[UserID], $action into @MergeLog;

    SELECT MergeAction, count(*)
    FROM @MergeLog
    GROUP BY MergeAction

    What am I missing?



  • On the first issue, since you only appear to be using this table to output data, you can simply declare it in the stored procedure instead of as a parameter, e.g.:

    CREATE PROCEDURE [dbo].[sp_tblUsers_Dim_InsertUpdate]
    AS
    BEGIN
      DECLARE @MergeAction TABLE ([UserId] INT, MergeAction VARCHAR(100))
      
      
    END
    

    For the second issue, your UPDATE statement is incorrect. Its listing the target columns but not what to set them as. They should be t.[column] = s.[column]. Also, you should remove t.[User_ID] from the UPDATE list since it is the key column you're matching on, so doesn't need to be updated.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2