0

Azure Synapse has the Bulk Insert option in its GUI for inserting tables.

https://i.stack.imgur.com/xweSL.png

But what is the underlying code that it is running? I would like to run it as TSQL rather than as a pipeline.

The documentation is unclear that is even supported while variations of this the below all fail

Running the following yields errors:

INSERT INTO [schema].[table][
SELECT * FROM OPENROWSET(
        BULK 'filename.parquet',
        FORMAT = 'PARQUET'
    )
Thom A
  • 88,727
  • 11
  • 45
  • 75
John Stud
  • 1,506
  • 23
  • 46
  • See https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16#j-importing-into-a-table-from-a-file-stored-on-azure-blob-storage – David Browne - Microsoft Nov 11 '22 at 17:05
  • This is not supported on Azure Synapse according to that URL – John Stud Nov 11 '22 at 17:42
  • For Synapse Dedicated SQL Pool use the COPY command https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest or Polybase with external tables. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-load-from-azure-blob-storage-with-polybase – David Browne - Microsoft Nov 11 '22 at 17:47
  • Right, but as you can see in the image, Synapse has Bulk Insert. So then, what are they using to accomplish this? Its not COPY INTO. – John Stud Nov 11 '22 at 17:54
  • That's a client-side bulk insert, like using SqlBulkCopy or BCP. It's not the fastest option for Synapse because all the data has to go to the TDS endpoint on the head node, whereas with PolyBase and COPY the compute nodes load directly from storage in parallel. – David Browne - Microsoft Nov 11 '22 at 17:56
  • Can you point to the documentation for that such that it can be executed via TSQL? I know its inefficient but it has better format /error handling than COPY INTO and so I would like to use it for edge cases. – John Stud Nov 11 '22 at 18:00
  • The client-server bulk loading APIs can't be used from TSQL. – David Browne - Microsoft Nov 11 '22 at 20:10

0 Answers0