How to import and export image/file column data in SQL Server?

Several methods are available for importing & exporting images / photos / videos / documents / BLOBs.
Here i have used "OPENROWSET" for importing image to database and BCP command (Bulk copy) for exporting. We can also use BCP command for import images/documents.

VARBINARY(MAX) Datatype using  for store images that consisting approximately 2 GB.

USE AdventureWorks;

GO

-- Create image warehouse for importing image into sql server database

CREATE TABLE dbo.PhotoLibrary
(

  PhotoLibraryID       INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  ImageName            VARCHAR(100),

  Photo                     VARBINARY(MAX)
)

GO

-- SQL Server import image - storing images to sql server database

INSERT INTO dbo.PhotoLibrary

           ([ImageName])

VALUES     ('nature.JPG')


GO

UPDATE dbo.PhotoLibrary

SET    Photo = (SELECT *

                FROM   OPENROWSET(BULK 'D:\image\photo\nature.JPG',

                       SINGLE_BLOB) AS x)

WHERE  [ImageName] = 'nature.JPG'

GO

-- Check table population

SELECT *

FROM   dbo.PhotoLibrary

GO


-- SQL Server export image - Using BCP Command (Bulk Copy)

DECLARE  @Command NVARCHAR(4000)


-- Keep the command on ONE LINE - SINGLE LINE!!!

SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary" queryout "D:\image\photo\natureNew.jpg" -T -n -S <Server name\Instance name>'


-- PRINT @Command -- debugging

EXEC xp_cmdshell  @Command

GO