Pages

Wednesday, May 25, 2011

Insert image in SQL Server in binary format without frontend

There are various ways to insert images into the database
CREATE TABLE myTable(Document varbinary(max))
INSERT INTO myTable(Document)
SELECT * FROM
OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)

-- or --
UPDATE table
SET column =
(SELECT * FROM
OPENROWSET(BULK N'D:\Filename.aspx', SINGLE_BLOB) AS ORS)
WHERE ID = 2

-- or --
You can also loop to insert images from a particular folder too
DECLARE @imgString varchar(80)
DECLARE @insertString varchar(3000)
SET @imgNumber = 1
WHILE @imgNumber < 101
BEGIN
SET @imgString = 'E:\images\Picture' + CONVERT(varchar,@imgNumber) + '.jpg'
SET @insertString = N'INSERT INTO images(imageData)
SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'

EXEC(@insertString)
SET @imgNumber = @imgNumber + 1
END
GO

No comments:

Post a Comment