Post reply

Select into from huge table with indexes

  • July 29, 2009 at 9:52 pm

    #136830

    The maximum system-generated unique value for a duplicate group was exceeded for index with partition

    ID 422227540443136. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

    I got this error while doing SELECT into from a huge table with 2.5 billion records using linked server... table had couple indexes as well both clustered and non-clustered... What should be a workaround to get that data? Thanks in advance for help.

  • July 30, 2009 at 7:38 am

    #1032379

    SELECT INTO with a linked server and that much data is going to cause you a lot of headaches. Linked servers move the data being processed from the linked server to the local server then they do the processing. You're basically accessing the data twice.

    To migrate this much data, you'd be better off with some sort of bulk load process using SSIS or bcp through sqlcmd or even the BULKINSERT command after exporting to a file.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • July 30, 2009 at 9:39 am

    #1032478

    I agree with Grant.... BCP out to a file at the source, BULK INSERT from the file to the destination. If you use the "NATIVE" option of BCP, it'll be wicked fast. It's what "replication" uses.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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