0

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?

Dan Guzman
  • 28,168
  • 2
  • 44
  • 68
Karen Schaefer
  • 11
  • 3
  • Hi, Karen! Welcome to DBA.SE. Would you mind formatting your code into a code block and adding some line breaks? it's difficult to read in its current form. – Brendan McCaffrey Mar 04 '22 at 21:40
  • I did but for some reason it will not display correctly. I tried both with the enter code here and ctrl K. neither worked. – Karen Schaefer Mar 04 '22 at 21:43
  • You can't return a table variable, you can only pass in a Table Valued Parameter as input. You could output into a #Temp temporary table, or you could just declare the @MergeLog table variable locally and select the results back to the client – Charlieface Mar 09 '22 at 14:48

1 Answers1

0

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

<merge statement>
  <select statement>
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.

HandyD
  • 9,942
  • 1
  • 11
  • 27