Insert Large Objects into Azure SQL Data warehouse

Question

I have created a table in Azure SQL Data Warehouse as below:

CREATE TABLE dbo.test_lob_type
(
    id          VARCHAR(80) NOT NULL,
    mime_type   VARCHAR(80) NOT NULL,
    binary_lob  VARBINARY(MAX) NULL
)
WITH
(
    DISTRIBUTION = HASH ( id ), 
    CLUSTERED INDEX ( id ASC )
);

I want to insert a BLOB object into this table. I tried to achieve this using the OPENROWSET command as pointed in the link How to insert a blob into a database using sql server management studio

But unfortunately this command does not work with Azure SQL DW. Can anyone provide any input on how to insert any BLOB object into a SQL DW table from the command line?

score 1 · Answer 1 · answered Dec 06 '16 at 14:11

bcp is supported for this scenario. Here is a simple example using SQL Authentication and char format:

REM Example using SQL Authentication and character file
bcp dbo.test_lob_type in test_lob_type.bcp -S yourDWServer.database.windows.net -d yourDWName -U yourLogin -P yourPassword -c

If your file only contains the blob, consider loading to a staging table before inserting into the main table.

Insert Large Objects into Azure SQL Data warehouse

1 Answers1