RE: Inserting Multiple Records Using While Loop

Old Hand

Points: 311

January 17, 2013 at 5:46 am

Thank you Gazareth. I looked again and realized I didn't even need the loop.

All I need is and really I don't even need the temp table just need to do the insert on the permanent table. I think my brain was on vacation yesterday.

create table #TempSalesGLLink(

AHInsCostAcct varchar(10)

, AHInsCostDept varchar(10)

, AHInsRevenueAcct varchar(10)

, AHInsRevenueDept varchar(10)

, CashDownAcct varchar(10)

, CashDownDept varchar(3)

, CLInsCostAcct varchar(10)

, CLInsCostDept varchar(10)

, CLInsRevenueAcct varchar(10)

, CLInsRevenueDept varchar(10)

, CostGoodsAcct varchar(10)

, CostGoodsDept varchar(3)

, CostofSalesSubAcct varchar(10)

, CostofSalesSubDept varchar(10)

, DueFromFinAcct varchar(10)

, DueFromFinDept varchar(3)

, DueToCustAcct varchar(10)

, DueToCustDept varchar(3)

, InvSoldAcct varchar(10)

, InvSoldDept varchar(3)

, LineCd char(2)

, LoanDiscountAcct varchar(10)

, LoanDiscountDept varchar(3)

, LocId varchar(10)

, NSFFeeAcct varchar(10)

, NSFFeeDept varchar(3)

, NSFRecAcct varchar(10)

, NSFRecDept varchar(10)

, OtherIncomeAcct varchar(10)

, OtherIncomeDept varchar(3)

, OverAllowAcct varchar(10)

, OverAllowDept varchar(3)

, SalesGLLinkUniqueNum int

, SalesTypeCd varchar(5)

, StoreId varchar(10)

, TradeACVAcct varchar(10)

, TradeACVDept varchar(3)

, VehSaleAcct varchar(5)

, VehSaleDept varchar(3)

)

--CONSTRAINT [PK_SalesGLLink] PRIMARY KEY

--(

--[StoreId] ASC,

--[LocId] ASC,

--[SalesGLLinkUniqueNum] ASC

--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

--) ON [PRIMARY]

SET NOCOUNT ON

Insert Into #TempSalesGLLink(StoreId, LocId, SalesGLLinkUniqueNum, SalesTypeCd, LineCd, VehSaleAcct, VehSaleDept, TradeACVAcct, TradeACVDept, OverAllowAcct, OverAllowDept, LoanDiscountAcct, LoanDiscountDept,

DueFromFinAcct, DueFromFinDept, CashDownAcct, CashDownDept, CostGoodsAcct, CostGoodsDept, InvSoldDept, OtherIncomeAcct, OtherIncomeDept, DueToCustAcct, DueToCustDept, NSFFeeAcct, NSFFeeDept, NSFRecAcct,

NSFRecDept, CostofSalesSubAcct, CostofSalesSubDept, AHInsRevenueAcct, AHInsRevenueDept, AHInsCostAcct, AHInsCostDept, CLInsRevenueAcct, CLInsRevenueDept, CLInsCostAcct, CLInsCostDept)

select StoreId, LocId, 6, SalesTypeCd, '4' as LineCd, VehSaleAcct, VehSaleDept, TradeACVAcct, TradeACVDept, OverAllowAcct, OverAllowDept, LoanDiscountAcct, LoanDiscountDept,

NSFRecDept, CostofSalesSubAcct, CostofSalesSubDept, AHInsRevenueAcct, AHInsRevenueDept, AHInsCostAcct, AHInsCostDept, CLInsRevenueAcct, CLInsRevenueDept, CLInsCostAcct, CLInsCostDept from SalesGLLink where salestypecd = 'r' and linecd = '3'

NSFRecDept, CostofSalesSubAcct, CostofSalesSubDept, AHInsRevenueAcct, AHInsRevenueDept, AHInsCostAcct, AHInsCostDept, CLInsRevenueAcct, CLInsRevenueDept, CLInsCostAcct, CLInsCostDept)

select StoreId, LocId, 7, SalesTypeCd, '5' as LineCd, VehSaleAcct, VehSaleDept, TradeACVAcct, TradeACVDept, OverAllowAcct, OverAllowDept, LoanDiscountAcct, LoanDiscountDept,