Copy Between 2 and 3 billion rows from a heap table with no key to a new partioned table.

  • Hello,

    I need to copy Between 2 and 3 billion rows from a heap table with no identity to a new partioned table. The primary key is made up of a smallint value that represents a Month and BIGINT a loan_number.

    --The partion key is a small int value.

    --I have tried the approach of selecting by the partition key, but as you can imagine because of the amount of data each select can take quite some time.

    --I also tried using an import\export task it started moving some data, but it is also extremly slow.

    --The new table has 24 partitions. each partition represents 24 values of the partition key from 0 - 552

    Here is the definition of the partitioned table. The heap table is the same except it does not have the identity column created_by\date attributes.

    --Partitioned table

    CREATE TABLE dbo.qc_partition

    (

    qc_partition_idBIGINT IDENTITY(1,1)

    ,pool_idCHAR(3)NOT NULL

    ,deal_noCHAR(5)NOT NULL

    ,group_noCHAR(3)NOT NULL

    ,servicerVARCHAR(4)NULL

    ,internal_loan_idCHAR(6)NOT NULL

    ,external_loan_idVARCHAR(18)NOTNULL

    ,last_int_pSMALLDATETIMENULL

    ,balanceMONEYNULL

    ,int_rateNUMERIC(6, 3)NULL

    ,total_payment_dueMONEYNULL

    ,sched_principalMONEY NULL

    ,sched_mnth_pMONEYNULL

    ,mba_statVARCHAR(1)NULL

    ,ots_statVARCHAR(1)NULL

    ,payment_histVARCHAR(12)NULL

    ,exceptionVARCHAR(1)NULL

    ,start_dateSMALLDATETIMENULL

    ,end_dateSMALLDATETIMENULL

    ,fc_end_typVARCHAR(1)NULL

    ,payoff_dSMALLDATETIMENULL

    ,payoff_rVARCHAR(1)NULL

    ,sell_dateSMALLDATETIMENULL

    ,inv_balMONEYNULL

    ,next_percentNUMERIC(6, 3)NULL

    ,loss_valMONEYNULL

    ,net_rateNUMERIC(7, 4)NULL

    ,periodSMALLINTNOT NULL

    ,file_nameCHAR(8)NOT NULL

    ,created_byVARCHAR(70)NOT NULLDEFAULT CURRENT_USER

    ,created_dateDATETIMENOT NULLDEFAULT (GETDATE())

    ) ON partition_scheme_qc (period)

    One more question about the indexing. I created the following index, but was wondering if anyone had better idea on that. Also, What would be the best way to create a clustered-index\primary key here.

    CREATE NONCLUSTERED INDEX IX_period_external_loan_id

    ON qc_partition (period, external_loan_id)

    ON partition_scheme_qc (period)

    GO

    Does anyone have any ideas?

    Thank you for your help

  • Related post. Direct replies here:

    http://www.sqlservercentral.com/Forums/Topic1405106-146-1.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • This is a duplicate post. Posting the same question over and over isn't the thing to do here. All it does is split resources and tick people off.

    Please don't post any answers on this thread... instead, post them where this seems to have started....

    http://www.sqlservercentral.com/Forums/Topic1405106-146-1.aspx

    --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)

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

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