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.

OPENROWSET BULK

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

CREATE TABLE documentTable(Document varbinary(max)) 

INSERT INTO documentTable(Document) 
SELECT * FROM 
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:

• Insert binary data like images into SQL Server without front-end application
• MSDN:OPENROWSET (Transact-SQL) (See
  D. Using OPENROWSET to bulk insert file data into a varbinary(max) column)

SSIS

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') 
 WITH (
  
  [Document] varchar(max) '@Document'
  )

EXEC sp_xml_removedocument @idoc