Monday, August 23, 2010

Inserting file data into a varbinary(max) column

The following are ways I've found to import binary file data into a VARBINARY column on SQL Server 2005.


This method avoids the need for a front end tool such as SSIS.

CREATE TABLE documentTable(Document varbinary(max)) 

INSERT INTO documentTable(Document) 
OPENROWSET(BULK N'C:\ImageFile.png', SINGLE_BLOB) as imagedata

Note: The file path is with respect to the SQL Server. I.e. it is loading a image file from the servers file system.

See Also:


If SSIS is available another option was to use a SSIS package as per SSIS – Importing Binary Files Into A VARBINARY(MAX) Column.

Scripting out to XML with base64 encoding

SELECT * FROM [documentTable] for xml raw, BINARY BASE64

DECLARE @xmlDoc xml
SET @xmlDoc = 


This data can then be scripted in.

DECLARE @xmlDoc xml
SET @xmlDoc = 


DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc

INSERT INTO [myTable] ([Document])
SELECT [udfBase64Decode]([Document]) as [Document] FROM openxml(@idoc, '/ROOT/row') 
  [Document] varchar(max) '@Document'

EXEC sp_xml_removedocument @idoc