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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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