/*
Создаем для таблицы Appointment новые поля CRM_Activity_id_2016, CRM_Activity_id_2011
*/

ALTER TABLE [PIK_DWH].[dbo].[Appointment]
DROP COLUMN IF EXISTS [CRM_Activity_ID_2016]
    ,COLUMN IF EXISTS [CRM_Activity_ID_2011];

ALTER TABLE [PIK_DWH].[dbo].[Appointment]
ADD [CRM_Activity_ID_2016] UNIQUEIDENTIFIER NULL
   ,[CRM_Activity_ID_2011] UNIQUEIDENTIFIER NULL;

/*
Заполняем поля сущности CRM_Activity_id_2016 и CRM_Activity_id_2011
*/

UPDATE [trg]
SET [trg].[CRM_Activity_ID_2016] = [src].[CRM_ActivityId]
FROM  [PIK_DWH].[dbo].[Appointment] AS [trg]
JOIN [PIK_Stage].[crm].[view_Appointment_new] AS [src] ON CAST([src].[CRM_ActivityId] AS UNIQUEIDENTIFIER) = CAST([trg].[CRM_ActivityId] AS UNIQUEIDENTIFIER) -- CRM2016
WHERE   [trg].[CRM_Activity_ID_2016] IS NULL

UPDATE [trg]
SET [trg].[CRM_Activity_ID_2011] = [src].[CRM_ActivityId]
FROM  [PIK_DWH].[dbo].[Appointment] AS [trg]
JOIN [PIK_Stage].[crm].[view_Appointment_new] AS [src] ON CAST([src].[CRM_ActivityId] AS UNIQUEIDENTIFIER) = CAST([trg].[CRM_ActivityId] AS UNIQUEIDENTIFIER) -- CRM2016
WHERE   [trg].[CRM_Activity_ID_2011] IS NULL

UPDATE [trg]
SET [trg].[CRM_Activity_ID_2011] = [src].[ActivityId]
FROM  [PIK_DWH].[dbo].[Appointment] AS [trg]
JOIN [PIK_Stage].[crm].[view_Appointment] AS [src] ON CAST([src].[ActivityId] AS UNIQUEIDENTIFIER) = CAST([trg].[CRM_ActivityId] AS UNIQUEIDENTIFIER) -- CRM2011
WHERE   [trg].[CRM_Activity_ID_2011] IS NULL