Results 1 to 9 of 9

Thread: Insert recs using Loop

  1. #1
    Join Date
    Nov 2010
    Posts
    8

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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Remove GO statements from the batch, once you have GO executed, all variable declarations are lost as well.

  3. #3
    Join Date
    Nov 2010
    Posts
    8
    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.

  4. #4
    Join Date
    Nov 2010
    Posts
    8

    Question

    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

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  6. #6
    Join Date
    Nov 2010
    Posts
    8

    Question 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

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  8. #8
    Join Date
    Nov 2010
    Posts
    8
    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.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    No, should create it with 'create table ...' instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •