# Miscellaneous > Structured Query Language (SQL) >  Insert recs using Loop

## abenitez77

I am getting error msg's with my syntax.  .  Can you help me out ?

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

Set @Recid = Select MAX(RECNO) as recno from dbo.as_AllStores 
GO
Drop Table dbo.as_Redemption_Offers_DayGap
GO
Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.* 
Into dbo.as_Redemption_Offers_DayGap
FROM dbo.as_offer_dates as ao  
	Inner Join (Select * 
				From dbo.as_all_division_coupons 
				Where @Recid = RECNO ) 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 (Select * 
				From dbo.as_all_division_coupons 
				Where @Recid = RECNO ) 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 (Select * 
				From dbo.as_all_division_coupons 
				Where @Recid = RECNO ) 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
GO
Set @Recid = @Recid - 1
GO
While @Recid > 0   
begin 

Insert Into dbo.as_Redemption_Offers_DayGap 	
	(Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.* 
	FROM dbo.as_offer_dates as ao  
		Inner Join (Select * 
					From dbo.as_all_division_coupons 
					Where @Recid = RECNO ) 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 (Select * 
					From dbo.as_all_division_coupons 
					Where @Recid = RECNO ) 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 (Select * 
					From dbo.as_all_division_coupons 
					Where @Recid = RECNO ) 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

End 


Error Msg:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Recid".
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@Recid".
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@Recid".

----------


## skhanal

Remove GO statements from the batch, once you have GO executed, all variable declarations are lost as well.

----------


## abenitez77

Ok, so I left 1 or 2 GO.  I removed them and now it runs...i have other things i need to add now before running it.

----------


## abenitez77

I want to create a temp table that I can use for my subquery in my loop, so that I don't have to run it 3 times for my links.  I tried it but get error msg.  I don't need it in the first query but in the loop, it will loop about 1,000 times and they are large tables.  Can someone help me with syntax and code?

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

Set @Recid = (Select MAX(RECNO) as recno from dbo.as_AllStores )
Set @StoreNum = (Select Store From dbo.as_AllStores Where RECNO = @Recid)
Drop Table dbo.as_Redemption_Offers_DayGap

Select 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.* 
Into dbo.as_Redemption_Offers_DayGap
FROM dbo.as_offer_dates as ao  
	Inner Join (Select * 
				From dbo.as_all_division_coupons 
				Where Store = @StoreNum) 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 (Select * 
				From dbo.as_all_division_coupons 
				Where Store = @StoreNum) 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 (Select * 
				From dbo.as_all_division_coupons 
				Where Store = @StoreNum) 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

While @Recid > 0   
begin 

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 
--Where Store = @StoreNum  


Insert dbo.as_Redemption_Offers_DayGap 	
	Select 'Redemption', ar3.*, 'Offers->', ao.* 
	FROM dbo.as_offer_dates as ao  
		Inner Join (Select * 
					From dbo.as_all_division_coupons 
					Where Store = @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 
		Inner Join (Select * 
					From dbo.as_all_division_coupons 
					Where Store = @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 
		Left Join (Select * 
					From dbo.as_all_division_coupons 
					Where Store = @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 

End

----------


## 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

----------


## rmiao

Should replace with:

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

Create temp table first then go to loop.

----------


## abenitez77

I do create the table the first time on top with this line:

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


then i delete the table and try to recreate it with the same query.... 

It works the first time...the second time is where it bombs out.

----------


## rmiao

No, should create it with 'create table ...' instead.

----------

