DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to store pictures in a database?

The following Microsoft SQL Server T-SQL script demonstrate how pictures, Pictures and photos be stored in the database. They can also be stored in the file system with reference only in the database.

USE AdventureWorks;

 

-- Create Picture warehouse for importing Picture into sql database

CREATE TABLE dbo.PictureWarehouse (

  PictureWarehouseID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  PictureName        VARCHAR(100),

  Photo            VARBINARY(MAX))

GO

 

-- SQL Server import Picture - sql storing Pictures database sql server

INSERT INTO dbo.PictureWarehouse

           ([PictureName])

VALUES     ('LEXINGTONAVE.JPG')

 

UPDATE dbo.PictureWarehouse

SET    Photo = (SELECT *

                FROM   OPENROWSET(BULK 'e:\Picture\photo\LEXINGTONAVE.JPG',

                       SINGLE_BLOB) AS x)

WHERE  [PictureName] = 'LEXINGTONAVE.JPG'

GO

 

-- Check table population

SELECT *

FROM   dbo.PictureWarehouse

GO

 

-- SQL Server export Picture

DECLARE  @SQLcommand NVARCHAR(4000)

 

-- Keep the command on ONE LINE - SINGLE LINE!!! - broken here for presentation

SET @SQLcommand = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PictureWarehouse"

                   queryout "e:\Picture\photo\expLEXINGTONAVE.jpg" -T -n -SPROD\SQL2005'

 

PRINT @SQLcommand -- debugging

 

EXEC xp_cmdshell   @SQLcommand

GO

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE