CODEWITHSUNDEEP
blobazure-synapse
indranil
indranil

Reputation: 83

Insert Large Objects into Azure SQL Data warehouse

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?

Upvotes: 1

Views: 207

Answers (1)

wBob
wBob

Reputation: 14389

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.

Upvotes: 1

Related Questions