Insert Binary Data into a SQL Server 2005 Column Straight from T-SQL (via OPENROWSET(BULK...) )
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...)
Posts
13 comments:
when i tried ur query i got this error "A correlation name must be specified for the bulk rowset in the from clause."
If you search google on that error you'll get a number of hits that may help you...
add AS i to the last line:
INSERT 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!
Really you are the big boss of the area!
It helped me a lot
Hi Greg,
I 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
Something 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.
And as a note: Don't use functions like "updatetext" if you use merge replication.
Regards.
i have the error message
Cannot bulk load. The file "C:\Documents and Settings\Bluryone\Desktop\imageDB.cgi" does not exist.
Do u have any idea to fix this??
@Blur
Maybe 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
how 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.
That 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
Post a Comment