Update and insert into to a mssql table from a temporary table 22.03.16

drop table #order
SELECT 
 h.[hp1_no_offer]
 ,l.[hp2_line]
 	   ,(Select TOP 1 o.[hz2_order_no]
	   FROM [SL].[dbo].[hz2_order_ln] AS o
	   LEFT JOIN [SL].[dbo].[hz1_order_hdr] AS oh
	   ON o.hz2_order_no = oh.F2_1hz_order_no
	   WHERE o.[hz2_style_code] = l.[hp2_style_code]
	   AND oh.hz1_name_customer = h.[hp1_name_customer]
	   AND oh.hz1_order_date > h.[hp1_date_offer]
	   ORDER BY oh.hz1_order_date asc
	   ) AS order_no
	   INTO #order 
 FROM [SL].[dbo].[hp1_quotation_hdr] AS h
 LEFT JOIN [SL].[dbo].[hp2_quotation_ln] AS l
 ON l.hp2_no_offer = h.hp1_no_offer
 WHERE h.[hp1_status_offer] = 1
 ORDER BY [hp1_no_offer]

SELECT hp1_no_offer
,order_no
,hp2_line
FROM #order 
WHERE order_no is not NULL

UPDATE [SL].[dbo].[zz_hp2_quotation_ln] 
SET status = 'generate_order'
FROM [SL].[dbo].[zz_hp2_quotation_ln] AS l
INNER JOIN #order o
ON l.hp2_no_offer_id = o.hp1_no_offer
AND l.hp2_line_id = o.hp2_line
WHERE o.order_no is not NULL

INSERT INTO zz_hp2_quotation_ln 
    (
      hp2_no_offer_id
	 ,hp2_line_id
	 ,comments
	 ,status
	 ,other
	 ,updated
	)
	SELECT hp1_no_offer
	,hp2_line
	,''
	,'generate_order'
	,''
	,''
	FROM #order AS o
	WHERE o.order_no is not NULL
	AND NOT EXISTS (SELECT *  FROM zz_hp2_quotation_ln  WHERE hp2_no_offer_id = hp1_no_offer AND hp2_line_id = hp2_line )

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7988eacc-4170-4f6f-8289-f10d4e4ea7b2/updating-a-table-with-data-from-a-temp-table?forum=transactsql

Anonymous

Update and insert into to a mssql table from a temporary table 22.03.16

Information Epoch 1746883066