Insert binary data like images into SQL Server without front-end application

I came across this solution for some months ago on another forum.
It is very handy, so I thought i should share it.

CREATE TABLE myTable(Document varbinary(max)) 
INSERT INTO myTable(Document) 
SELECT * FROM
OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)
Print | posted on Wednesday, September 26, 2007 12:35 PM

Feedback

# re: Insert binary data like images into SQL Server without front-end application

left by Jeff at 9/26/2007 4:50 PM
Wow .. it's that easy??? Very, very cool -- thanks Peter!!!

# re: Insert binary data like images into SQL Server without front-end application

left by Peso at 9/26/2007 9:38 PM
I don't think it work for SQL Server 2000.
The SINGLE_BLOB setting was introduced for SQL Server 2005, if I am not mistaking.

# re: Insert binary data like images into SQL Server without front-end application

left by Mladen at 9/27/2007 11:54 AM
yeah.. this is not possible in ss2k
i use this for fast xml import among other thing :)
http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

# re: Insert binary data like images into SQL Server without front-end application

left by mgarrettdev at 10/23/2007 8:24 PM
Great Post! --- I added a loop script to it for a bunch of images I needed to put into my db...

-----------------------------------
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
---------------------------------

Again, Thanks for the post

Best Regards

# re: Insert binary data like images into SQL Server without front-end application

left by Alex at 11/9/2007 2:14 AM
What is the easiest way in SQL Server 2000?

# re: Insert binary data like images into SQL Server without front-end application

left by Lokesh at 12/11/2008 8:07 AM
It gives an error:"A correlation name must be specified for the bulk rowset in the from clause."

# re: Insert binary data like images into SQL Server without front-end application

left by Peso at 12/11/2008 11:37 AM
Add a table alias for the OPENROWSET function.

# re: Insert binary data like images into SQL Server without front-end application

left by abid at 4/4/2009 9:14 AM
thank u dear..
i was looking for it since very long
now i got it
thx

# re: Insert binary data like images into SQL Server without front-end application

left by Blur at 4/22/2009 8:10 AM
What do u mean by
Add a table alias for the OPENROWSET function.
how?? i am a noob in SQL

# re: Insert binary data like images into SQL Server without front-end application

left by Amal De Silva at 5/3/2009 1:21 AM
this is great it is working in SQL Express 2005 . no change required.

# re: Insert binary data like images into SQL Server without front-end application

left by mynk at 5/9/2009 11:59 AM
What do u mean by
Add a table alias for the OPENROWSET function.
how?? i am a noob in SQL

Select * from OPENROWSET(BULK N'D:\aa.gif', SINGLE_BLOB) as image


') as image' is compulsory in above statement.you cannot negate it

# re: Insert binary data like images into SQL Server without front-end application

left by Gurpreet Singh Mental at 5/9/2009 1:23 PM
can u provide me for sql server 2000

# re: Insert binary data like images into SQL Server without front-end application

left by ker at 5/19/2009 9:25 AM
I have an error: Incorrect syntax near the keyword 'BULK'.

I'm using Sql Server 2005

# re: Insert binary data like images into SQL Server without front-end application

left by Peso at 5/19/2009 11:10 AM
Have you checked your compatibility level?
It must be set to 90 or higher.

# re: Insert binary data like images into SQL Server without front-end application

left by Kris at 6/2/2009 9:49 PM
Thanks alot.

This works too:

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

# re: Insert binary data like images into SQL Server without front-end application

left by vijay at 6/8/2009 11:49 AM
Hi,

I have a varbinary(max) column in my database table in which i am saving "test.docx" file (remember it;s a docx file, not doc). When i am performing a string search on this column then it is returning no result. The same search is running on "doc" files saved in binary format in database.

My search query is

Select * from <tablename>
where convert(varchar(max),<mybinarycolumn>) like '%<keywordtosearch>%';

is there a way i can perform a binary search on this table.

please reply asap.

# re: Insert binary data like images into SQL Server without front-end application

left by Peso at 6/8/2009 12:07 PM
You will have to convert to search parameter value to varbinary too.
Or use the new FILESTREAM datatype.
Title  
Name
Email (never displayed)
Url
Comments   
Please add 8 and 5 and type the answer here: