Post reply

Bulk Insert very large compressed partitioned table and primary key

  • October 4, 2010 at 6:52 am

    #228941

    Hi.

    I tried to load a very large .CSV file in primary key order to a Compressed partitioned table with the parameter ORDER by the primary key, but it takes it to the TempDB to sort it (Not enough space on tempdb).

    Is it possible to do this load? or it will always go to temp for sort.

    I am loading now into a compressed non-partitioned table and it seems that it will load, because it is not using tempdb.

    Any hints or tips?

    Can I do this very large Bulk insert to a Compressed and partitioned table?.

    Thanks

  • October 4, 2010 at 6:55 am

    #1230969

    Pablo Campanini (10/4/2010)

    Hi.

    I tried to load a very large .CSV file in primary key order to a Compressed partitioned table with the parameter ORDER by the primary key, but it takes it to the TempDB to sort it (Not enough space on tempdb).

    Is it possible to do this load? or it will always go to temp for sort.

    I am loading now into a compressed non-partitioned table and it seems that it will load, because it is not using tempdb.

    Any hints or tips?

    Can I do this very large Bulk insert to a Compressed and partitioned table?.

    Thanks

    ORDER BY will always force a SORT operation.

    Can you sort .csv file at the source? if file is already sorted you wouldn't need ORDER BY during load process.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • October 4, 2010 at 7:04 am

    #1230973

    Thank you for your response, but

    In the Bulk Insert command, I can specify the parameter ORDER, NOT ORDER BY.

    This parameter lets me load with the clustered primary key already created. Of course I do have the file already sorted on Primary key, and avoid a sort in the tempdb

    Questions is, Does the partition makes it sort by the fileld in which we partition?.

    It worked for me into non-partition tables, but into a partition table, it goes to temp.

    Help please

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

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