Share via

Azure synapse : Fastest way to load into Fact table

sakuraime 2,351 Reputation points
2020-12-03T06:42:29.127+00:00

what's the fastest way to load/insert in to Current fact table(NOT CREATE A NEW ONE) ?

– INSERT INTO … SELECT FROM …

– CREATE TABLE … AS SELECT …
– Polybase
– BCP
– OPENROWSET / BULK INSERT
– COPY INTO …

??

Azure Synapse Analytics
Azure Synapse Analytics

An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. David Browne - msft 3,851 Reputation points
    2020-12-07T21:36:06.203+00:00

    BCP loads through the head node, so definitely not as scalable as COPY INTO.

    1 person found this answer helpful.
  2. HimanshuSinha 19,597 Reputation points Microsoft Employee Moderator
    2020-12-04T02:28:08.997+00:00

    Hello @sakuraime ,
    Thanks for the ask and using thge forum .
    Since you mentioned that you do not want to create a new one , you should not consider the below will create the table .

    1. CREATE TABLE … AS SELECT …

    INSERT INTO … SELECT FROM … : This is a logged event as so it will be slower the below ones .
    BULK INSERT : This is minimally logged and so its will be slower then BCP .
    BCP should be the fastest among these .
    Polybase shoule be the fastest as polybase automatically parallelizes the data load process

    Thanks
    Himanshu

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.