Importing Images to SQL Server using T-SQL

Importing Images to SQL Server using T-SQL

Using OpenRowSet you can import Any file into database(pdf,excel,word,xps, etc.,) as a BLOB

Here is an example of importing/inserting images into database.

Step 1) Create City Table

CREATE TABLE [dbo].[City](
    [ID] [smallint] IDENTITY(1,1) NOT NULL,
    [CityName] [nvarchar](50) NULL,
    [Description] [nvarchar](max) NULL,
    [CityImage] [image] NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

It has city name,desc and city Image

Step 2) Insert records with images into   City Table

  insert into City(CityName,[Description],CityImage)
  values('New York',
  'New York  is a state in the Northeastern region of the United States.',
  (select c.* from OpenRowSet(BULK N'C:\Çity\New York.jpg',SINGLE_BLOB) as c))

assuming  New york image exists in C:\city

Step 3) Update image for already existing record

update City set CityImage=(select c.* from OpenRowSet(BULK N'C:\Çity\New York.jpg',SINGLE_BLOB) as c)
where ID=4

Tags:Importing Images to SQL Server using T-SQL,Import image into sql server using tsql,Insert images into sql server using DML, update image data using DML,DML insert,DML update