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) 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.
- 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)
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