How to insert in batches WITHOUT using @@ROWCOUNT

  • I have the specific insert query below, and it works fine if I have less than 20 records or so, but if I have millions of records to loop through,

    it is not going to work. I have the batches below in hundreds, but i can be 1000, ,2000, etc..

    Any idea how I can write this better WITHOUT using @@rowcount, and better performance would be very much appreciated.

    DECLARE @min-2 INT;

    DECLARE @max-2 INT;

    DECLARE @min2 INT;

    DECLARE @max2 INT;

    SET @min-2 = 1;

    SET @max-2 = 5;

    WHILE @min-2 <= @max-2

    IF @min-2 = 1 THEN

    SET @min2 = 1, @max2 = 100;

    ELSEIF @min-2 = 2 THEN

    SET @min2 = 101, @max2 = 200;

    ELSEIF @min-2 = 3 THEN

    SET @min2 = 201, @max2 = 300;

    ELSEIF @min-2 = 4 THEN

    SET @min2 = 301, @max2 = 400;

    ELSEIF @min-2 = 5 THEN

    SET @min2 = 401, @max2 = 500;

    END IF;

    INSERT INTO table2

    SELECT colum1, column2

    FROM table1

    WHERE id BETWEEN @min2 AND @max2;

    SET @min-2 = @min-2+1;

    END WHILE;

  • what is the aim of what your doing? are you just inserting everything from one table into another in a batch of N as thats what it looks like to me?

    you could run throuch a loop using select top N and inner joining on your tables to ensure the next batch dont already exist.

    ***The first step is always the hardest *******

  • I'm forced to agree with the above, this technique is usually best avoided if possible. What problem are you attempting to avoid by staggering your inserts like this?

    However, as mentioned above (and slightly inaccurately, sorry, you want an outer join) the best bet is typically to use something like this, as long as you have some kind of business key you can rely on. Better would be to use a MERGE statement if you're updating from the System of Record to a warehouse and do inserts/updates simultaneously.

    DECLARE @ROWCOUNT INT, @InsertVolume INT

    SET @ROWCOUNT = 1

    SET @InsertVolume = 5000 --Set to # of rows

    WHILE @ROWCOUNT > 0

    BEGIN

    INSERT INTO TargetTable

    SELECT TOP @InsertVolume

    --ColumnList

    FROM

    staging

    LEFT JOIN

    Target

    ON

    staging.id = target.ID

    WHERE

    staging.id IS NULL

    END

    However, it doesn't look like you have any restrictors on your inserted data, it's just a blind dump from staging with staggered row insertions. Some more information on the process you're trying to do and why you're doing it would definately help us narrow down your best solution here.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply