Insert recs using Loop

Printable View

11-15-2010, 10:19 AM
abenitez77

Insert recs using Loop

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".
11-15-2010, 11:31 AM
skhanal

Remove GO statements from the batch, once you have GO executed, all variable declarations are lost as well.
11-15-2010, 12:18 PM
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.
11-15-2010, 03:58 PM
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
11-16-2010, 03:00 PM
skhanal

What error did you get?
11-16-2010, 03:32 PM
abenitez77

Using temp table for inner join

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
11-17-2010, 08:21 AM
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.
11-17-2010, 05:40 PM
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.
11-18-2010, 08:31 AM
rmiao

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