Thinking outside the box - 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)"[Post Leached in full]
A handy SQL Server 2005 tip...
Related BOL links OPENROWSET (Transact-SQL), Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...)
when i tried ur query i got this error "A correlation name must be specified for the bulk rowset in the from clause."
ReplyDeleteIf you search google on that error you'll get a number of hits that may help you...
ReplyDeleteadd AS i to the last line:
ReplyDeleteINSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB) AS i
This worked for me!
you are the big boss of the area!
ReplyDeleteReally you are the big boss of the area!
ReplyDeleteIt helped me a lot
Hi Greg,
ReplyDeleteI was looking for a solution to include the file to insert in a varbinary(max) in the T-SQL file (like you can do with the 'updatetext' command to a 'textptr' if it's not a varbinary(max))
Any ideas?
Thanks,
Wiebe
@Wiebe
ReplyDeleteSomething like this? (scroll toward the bottom)
Update text/ntext or varchar(max) data-type columns in SQL Server
The magic is the new .Write function.
Hope this helps,
Greg
Thanks.
ReplyDeleteAnd as a note: Don't use functions like "updatetext" if you use merge replication.
Regards.
i have the error message
ReplyDeleteCannot bulk load. The file "C:\Documents and Settings\Bluryone\Desktop\imageDB.cgi" does not exist.
Do u have any idea to fix this??
@Blur
ReplyDeleteMaybe try a path that the SQL Server has direct access to?
Copy the given file to the actual SQL Server and then use that path.
OPENROWSET is SQL Server reaching out to grab the resource, so if SQL Server can't access the path then you're going to get an error...
Does that make sense?
I'm getting this error
ReplyDeletehow to overcome
Please help me
insert into Mem_Image (Image)
select BulkColumn
from openrowset(Bulk'C:\a.jpeg',Single_Blob)
A correlation name must be specified for the bulk rowset in the from clause.
@PriyaSivakumar If you search on that error there's a number of good hits. Here's the first one A correlation name must be specified for the bulk rowset in the from clause.
ReplyDeleteThat post restarts pretty much restarts the above comments...
Try something like;
insert into Mem_Image(Image)
select *
from openrowset(Bulk'C:\a.jpeg',Single_Blob) As I
how to update the image...in sql server 2005
ReplyDelete