CREATE OR ALTER PROCEDURE [HM].[CopyOwnFieldsFromDeliveryToDelivery] @id_from INT, @id_to INT --@id_from: id MM- @id_to: id MM+
AS
DECLARE @object_id_delivery INT;
SELECT @object_id_delivery = object_id
FROM sys.objects
WHERE name = 'HMF_WarehouseDeliveryClassification';
--1419256211
DECLARE @kolumny VARCHAR(MAX) =
        (
            SELECT STUFF(
                   (
                       SELECT ', ' + s.name
                       FROM sys.columns s
                       WHERE s.object_id = @object_id_delivery AND column_id > 4
                       FOR XML PATH('')
                   ),
                   1,
                   2,
                   ''
                        ) AS name
        );

DECLARE @kolumny_tmp VARCHAR(MAX) =
        (
            SELECT STUFF(
                   (
                       SELECT ', tmp.' + s.name
                       FROM sys.columns s
                       WHERE s.object_id = @object_id_delivery AND column_id > 4
                       FOR XML PATH('')
                   ),
                   1,
                   2,
                   ''
                        ) AS name
        );


DECLARE @dynamic_sql VARCHAR(MAX) = 
' SELECT wdc.ElementId, ' + @kolumny + 
' INTO #temp_table FROM hm.mz mz
  JOIN hm.pw pw on mz.id = pw.idmg
  JOIN hm.dw dw on dw.id = pw.iddw
  JOIN SSCommon.HMF_WarehouseDeliveryClassification wdc on wdc.ElementId = dw.id
  WHERE mz.super =' + CONVERT(VARCHAR(20), @id_from) +
' INSERT INTO SSCommon.HMF_WarehouseDeliveryClassification (Guid,ElementId,st_last_modified,st_shadowdata, '+ @kolumny +')
  SELECT NEWID() as Guid, dw.id as ElementId,GetDate() as st_last_modified,null as st_shadowdata,'+ @kolumny_tmp +'
  FROM hm.pw pw
  JOIN hm.dw dw on dw.id = pw.iddw
  JOIN #temp_table tmp on dw.iddw = tmp.ElementId
  LEFT JOIN SSCommon.HMF_WarehouseDeliveryClassification wdc on wdc.ElementId = dw.id
  WHERE pw.iddkmg ='+ CONVERT(VARCHAR(20), @id_to) +' and pw.subtyp = 82 and wdc.Guid is null';

EXECUTE (@dynamic_sql);


