Convert a file URL to file byte in using a stored procedure?-CodePudding

My database has a table of person, and on that table there is a column named PersonImageUrl (which is hosted in a public container in azure). I have an upcoming migration to a new database. The new column in the new database table requires VARBINARY(max). I want to create a stored procedure to convert the contents of PersonImageUrl (file where the URL points to) into a byte array so that it would meet the requirements of my migration. Is this possible?

CodePudding user response：

Azure SQL Database (and SQL Server 2017 ) has a built-in integration to Azure BLOB storage, and for a public container you don't even need a credential, Eg:

CREATE EXTERNAL DATA SOURCE BlobStore
WITH 
(
   TYPE = BLOB_STORAGE,
   LOCATION = 'https://MyStorageAccount.blob.core.windows.net'
);


SELECT BulkColumn Image 
FROM OPENROWSET
(
   BULK 'MyPublicContainer/someimage.jpg',
   DATA_SOURCE = 'BlobStore',
   SINGLE_BLOB
) AS blob;

Examples of bulk access to data in Azure Blob storage