Insert recs using Loop

[skhanal]

What error did you get?

[abenitez77]

ok, so this is the code I'm working with now...The error msg I get is this(I highlighted line 60 in red below):
Msg 2714, Level 16, State 1, Line 60
There is already an object named '##as_OneStore' in the database.

Code:

Declare @StoreNum int
Declare @StoreCnt int
Declare @Recid int
Declare @RecCount int

Set @Recid = (Select MAX(RECNO) as recno from dbo.as_AllStores )
-- Get the Store number to use for subquery below.
Set @StoreNum = (Select Store From dbo.as_AllStores Where RECNO = @Recid)

IF OBJECT_ID(N'tempdb..as_Redemption_Offers_DayGap', N'U') IS NOT NULL
DROP TABLE as_Redemption_Offers_DayGap ;

IF OBJECT_ID(N'tempdb..dbo.##as_OneStore', N'U') IS NOT NULL
DROP TABLE dbo.##as_OneStore ;

Select *
Into dbo.##as_OneStore
From dbo.as_all_division_coupons_count
Where Store = @StoreNum

Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.*
Into dbo.as_Redemption_Offers_DayGap
FROM dbo.as_offer_dates as ao
@StoreNum) AS ar1
Inner Join dbo.##as_OneStore as ar1
ON ao.[date] - 1 = ar1.SCAN_DATE_CONVERT AND
ao.DivNum = ar1.Kma_div_nbr AND
ao.cpnnum = ar1.coupon_nbr
@StoreNum) AS ar2
Inner Join dbo.##as_OneStore as ar2
ON ao.[date] + 1 = ar2.SCAN_DATE_CONVERT AND
ao.DivNum = ar2.Kma_div_nbr AND
ao.cpnnum = ar2.coupon_nbr
@StoreNum) As ar3
Left Join dbo.##as_OneStore as ar3
ON ao.[date] = ar3.SCAN_DATE_CONVERT AND
ao.DivNum = ar3.Kma_div_nbr AND
ao.cpnnum = ar3.coupon_nbr
WHERE ar3.STORE IS NULL

Set @Recid = @Recid - 1

Drop Table dbo.##as_OneStore

While @Recid > 0
begin

-- Get the next store number to query in the Join below.
Set @StoreNum = (Select Store From dbo.as_AllStores Where RECNO = @Recid)

-- Put into a temp table to reuse in bottom query
Select *
Into dbo.##as_OneStore
From dbo.as_all_division_coupons_count
Where Store = @StoreNum

Insert dbo.as_Redemption_Offers_DayGap
Select 'Redemption', ar3.*, 'Offers->', ao.*
FROM dbo.as_offer_dates as ao
Inner Join dbo.##as_OneStore As ar1
ON ao.[date] - 1 = ar1.SCAN_DATE_CONVERT AND
ao.DivNum = ar1.Kma_div_nbr AND
ao.cpnnum = ar1.coupon_nbr
Inner Join dbo.##as_OneStore As ar2
ON ao.[date] + 1 = ar2.SCAN_DATE_CONVERT AND
ao.DivNum = ar2.Kma_div_nbr AND
ao.cpnnum = ar2.coupon_nbr
Left Join dbo.##as_OneStore As ar3
ON ao.[date] = ar3.SCAN_DATE_CONVERT AND
ao.DivNum = ar3.Kma_div_nbr AND
ao.cpnnum = ar3.coupon_nbr
WHERE ar3.STORE IS NULL

Set @Recid = @Recid - 1

Drop Table dbo.##as_OneStore

End