Excel Sheet should have 2 columns: Item Number and Price
="Select 'Q4' [PromoName], @cust [CustID], '" & A2 & "' [ProductCode], " & ROUND(B2,2) & " [Price], '2022-10-13'[StartDate], '2022-12-31' [EndDate] union"
BEGIN TRAN INSERT INTO EDI_PromoPricing (PROMO, CORPCUSTID, ProductCode, PRICE, StartDate,ENDDATE)
SELECT * FROM(
SELECT ''[PROMO], '' [CUSTID], '' [PROD], 0 [PRICE], CONVERT(date, GETDATE()-1) [START], CONVERT(date, GETDATE()+1) [End], '' [Notes] UNION
SELECT ''[PROMO], '' [CUSTID], '' [PROD], 0 [PRICE], CONVERT(date, GETDATE()-1) [START], CONVERT(date, GETDATE()+1) [End], '' [Notes] UNION
SELECT ''[PROMO], '' [CUSTID], '' [PROD], 0 [PRICE], CONVERT(date, GETDATE()-1) [START], CONVERT(date, GETDATE()+1) [End], '' [Notes]
)M
BEGIN TRAN update EDI_PromoPricing
Set ProductGroupCode= (Select ProductGroupCode from product p where EDI_PromoPricing.ProductCode=p.ProductCode),
BrandID=(Select BrandID from product p where EDI_PromoPricing.ProductCode=p.ProductCode)
Where ProductGroupCode is null and BrandID is null
------------------------------------
------------------------------------
Use [DealerWeb]
-- Drop table #ediCusts
Select * INTO #ediCusts
from(
Select 'CPS34460000' [CustID], 0 Processed union
Select '98884000' [CustID], 0 Processed union
Select 'CPS34887000' [CustID], 0 Processed union
Select '87100000' [CustID], 0 Processed union
Select '71170000' [CustID], 0 Processed union
Select 'CPS15841000' [CustID], 0 Processed union
Select '73490000' [CustID], 0 Processed union
Select '21810000' [CustID], 0 Processed
--Select 'KUSA001048' [CustID], 0 Processed
)x
-- Select * from #ediCusts
-- Select * from EDI_PromoPricing
While exists (select top 1 1 from #ediCusts where Processed=0)
BEGIN
Declare @cust nvarchar(50) = (select top 1 CustID from #ediCusts where Processed=0)
Insert into EDI_PromoPricing
SElect * from (
)x
Update #ediCusts set Processed=1 where CustID=@Cust
END