Select into with a loop

  • Please help me to write a query. This is the task. I have Table a and B. Table B has millions of rows.

    I want to insert into Table A by selecting from Table 10,000 records at a time until all the millions rows are inserted into Table A using a while loop.

  • Edit: Nvm wrong advice.

    Cheers,

    J-F

  • Anthonykush (7/21/2010)


    Please help me to write a query. This is the task. I have Table a and B. Table B has millions of rows.

    I want to insert into Table A by selecting from Table 10,000 records at a time until all the millions rows are inserted into Table A using a while loop.

    while exists (select PK from TableB EXCEPT select PK from TableA)

    insert into TableA <column_list>

    select top (10000) <column list>

    from TableB b

    LEFT JOIN TableA a

    ON a.PK = b.PK

    where a.PK IS NULL

    Obviously, you need to fill in a lot of variables here, but this give you the general idea.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Ok I assume that your source table has no "anchor" column which can be used to count your inserting progress...

    So, here an example.

    Lets crerate such a table, you will see I've concatinated 'K' to the number to make non identity kind of PK:

    SELECT TOP 1000000 'K' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as VARCHAR(10)) PKCol,

    c1.* INTO TableA

    FROM sys.COLUMNS c1 CROSS JOIN sys.COLUMNS c2

    Then prepare an empty copy of table:

    SELECT * INTO TableB

    FROM TableA WHERE 1 = 0

    Now, in order to insert 10000 rows at the time we need to some how track the progress, see how I introduce "anchor" column on the fly by using ROW_NUMBER:

    DECLARE @rn INT

    DECLARE @ac INT

    SET @rn = 0

    WHILE @rn = 0 OR @ac > 0

    BEGIN

    INSERT INTO TableB

    SELECT TOP 10000 ta.*

    FROM TableA ta

    JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PKCol) AS rn, PKCol FROM TableA) ota

    ON ota.PKCol = ta.PKCol

    WHERE rn > @rn

    SELECT @ac = @@ROWCOUNT, @rn = @rn + 10000

    END

    If your PK is a composite, remember to includwe all parts of it in OVER(ORDER BY) and JOIN.

    The above query does not pretend to be very fast, however inserting 10000 records at time helps to avoid having "transaction log full" problem 😀

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Thank you for this. I will try

  • One more help please.

    I am getting invalid column name PKCol after Table 1 and Table 2 with the actual table names.

    What is PKCol? is this the primary key for my table?

  • TonyK_Ga (7/21/2010)


    One more help please.

    I am getting invalid column name PKCol after Table 1 and Table 2 with the actual table names.

    What is PKCol? is this the primary key for my table?

    You didn't provide any information so he made up names. It is meant to convey that you're joining against the primary key to guarantee that you're not duplicating your inserts.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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