Loop with Insert

[abenitez77]

I am getting error msg's with my syntax. . Can you help me out ?Declare @StoreNum int Declare @StoreCnt int Declare @Recid intSet @Recid = Select MAX(RECNO) as recno from dbo.as_AllStores GODrop Table dbo.as_Redemption_Offers_DayGapGOSelect 'Redemption' As Redemption, ar3.*, 'Offers->' As Offers, ao.* Into dbo.as_Redemption_Offers_DayGapFROM 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 NULLGOSet @Recid = @Recid - 1GOWhile @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 - 1End Error Msg:Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'Select'.Msg 137, Level 15, State 2, Line 6Must declare the scalar variable "@Recid".Msg 137, Level 15, State 2, Line 12Must declare the scalar variable "@Recid".Msg 137, Level 15, State 2, Line 18Must declare the scalar variable "@Recid".Msg 137, Level 15, State 2, Line 1Must declare the scalar variable "@Recid".Msg 137, Level 15, State 2, Line 1Must declare the scalar variable "@Recid".Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'Select'.Msg 137, Level 15, State 2, Line 9Must declare the scalar variable "@Recid".Msg 137, Level 15, State 2, Line 15Must declare the scalar variable "@Recid".Msg 137, Level 15, State 2, Line 21Must declare the scalar variable "@Recid".Msg 137, Level 15, State 2, Line 27Must declare the scalar variable "@Recid".

 

[abenitez77]

This is more readable:

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".

 

[gmmastros]

The GO's you have in this code are causing problems. For example, run this in a query window.

Declare @Recid int
Set @RecId = 1
Select 'Before GO', @RecId As RecId
go

Select 'After GO', @RecId As RecId

In the results tab, you will see:
'Before GO',1

In the Messages tab, you'll see:

[tt][red]Must declare the scalar variable "@RecId".[/red][/tt]

I suggest you remove the GO's from your code and try again.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[abenitez77]

Ok, so I removed all my GO commands. 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

 

[SQLBill]

What is the error message?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875