Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    8

    Unanswered: batching without using a temp table

    Hello Friends,

    I have come to this forum seeking suggestions/expertise on the below issue I am facing.

    I need to transfer data from one table to another.

    To explain it more, I need transfer rows from a table (named holdings table) where sourcecode=100 AND date=getdate() to another table. The holdings table has an identity column on it called holdingId and has clustered index on this id column.

    The SP currently selects all the relevant rows into a temp table and then in a while loop, select 5000 rows from this temp table using identity column created on this temp table. Something like

    Select identity,*
    into #temp
    from holdings
    where sourcecode=100
    and date=getdate()

    declare @min int

    select @min=-5000

    while ( @@rowcount>0)
    begin

    set @min = @min+5000

    insert snapshot
    select *
    from #temp
    where id>@min
    And id<@min+5000

    end



    That SP now needs to be marked for replication. The problem is that creation of temp table is not allowed in SPs that are replicated.

    Please advise me on how this can be done without using any temp table. Also let me know if you need to more info on this or I am not clear.

    Regards
    Last edited by Nishant Gourav; 03-31-12 at 17:03.

  2. #2
    Join Date
    Feb 2012
    Location
    DUBAI
    Posts
    6

    Mark the procedure for replication

    Hi,

    You can put all the statements in a procedure and mark the procedure for replication. while the procedure is called at primary instead of sending the ddl the procedure will be executed in the standby server.

    -- akhilesh

  3. #3
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34
    Hi ,
    create your temp table in 'tempdb' database and drop it at the end of procedure since result set of tempdb database table can get share with multiple user.

    Select identity,*
    into tempdb..temp
    from holdings
    where sourcecode=100
    and date=getdate()

    declare @min int

    select @min=-5000

    while ( @@rowcount>0)
    begin

    set @min = @min+5000

    insert snapshot
    select *
    from tempdb..tempwhere id>@min
    And id<@min+5000

    end

Posting Permissions

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