How to import and export binary LOB into and from SQL server

Part 1 - Insert image into database

CREATE TABLE Documents (DocID int, Doc varbinary(max))

INSERT INTO Documents (DocID,Doc)
SELECT 1,* FROM OPENROWSET(BULK N’C:\temp2\pic1.gif’, SINGLE_BLOB) as AXA

select * from dbo.Documents

Part 2 - Documents.fmt

9.0
1
1       SQLBINARY     0       0       “”   1     Doc                                       “”
Part 3 -Export image into gif of jpg file or any other format

–EXEC usp_ExportBinaryFiles 1,’C:\temp\’
–BCP “SELECT Doc FROM TRACES.dbo.Documents WHERE DocId =1″ queryout C:\temp\Alex-01.gif -S A70195\Dev -T -fC:\temp2\Documents.fmt

EXEC master..xp_cmdshell ‘BCP “SELECT Doc FROM TRACES.dbo.Documents WHERE DocId =1″ queryout C:\temp\Dinesh-02.gif -S (local) -T -fC:\temp2\Documents.fmt’

ALTER  PROCEDURE usp_ExportBinaryFiles(
@DocID INT = NULL,
@OutputFilePath VARCHAR(500) = ‘C:\’
)
AS
BEGIN

DECLARE @sql VARCHAR(8000)

BEGIN
SELECT @sql = ‘BCP “SELECT Doc FROM TRACES.dbo.Documents WHERE DocId =’
+ CAST(DocId AS VARCHAR(5)) + ‘” queryout ‘ + @OutputFilePath
+ ‘Alex-01′ + ‘.’ + ‘gif’ + ‘ -S (local) -T -fC:\temp2\Documents.fmt’
FROM dbo.Documents
WHERE DocID = @DocID

PRINT @sql
EXEC xp_cmdshell @sql,NO_OUTPUT
END

END
GO