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.
it does not like the @MergeLog declaration
it errors on the commas from the Update statement
CREATE PROCEDURE [dbo].[sp_tblUsers_Dim_InsertUpdate] @MergeLog TABLE AS BEGINMERGE [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)) ENDFor 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 removet.[User_ID]from the UPDATE list since it is the key column you're matching on, so doesn't need to be updated.
