Monday, May 30, 2011

Inserting files to database

inserting data from files (jpg, xml, avi ....) into database, easy by the follwing code.

INSERT INTO Photos (FileName, FileType, Picture)

SELECT 'Photo1.jpg' AS FileName,
'.jpg' AS FileType,
* FROM OPENROWSET(BULK N'C:\Photo1.jpg', SINGLE_BLOB) AS Picture

as you see, in the section of bulk you put the path od the file that you want to put it in your database. any kind of file you can put. but you need to specify your data type column to varbinary(max) .

the following code try to insert multiple pics into database.

Declare @imgNumber int

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: