How to insert binary file into a binary SQL column?

Question

I have a SQL Server 2008 database with a table containing an image column.

Any idea how to write query to insert bin file to the image field?

Thank you in advance!

ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. — marc_s
– marc_s, Commented Jan 6, 2013 at 15:51
Where from, where to, what langauge is your application in? THe last is important for sample code is that is from an application. Right now - TSQL alone wont really be used for binary inserts in 99% of the cases without something around it. — TomTom
– TomTom, Commented Jan 6, 2013 at 16:28
FILESTREAM is something you might benefit from knowing something about. technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx — MarkD
– MarkD, Commented Jan 6, 2013 at 16:58

Hamlet Hakobyan · Accepted Answer · 2013-01-06 16:02:26Z

1

Totally agree with marc_s.

But your query will be something like this.

INSERT yourtable(ImageColumn)
SELECT image FROM
OPENROWSET(BULK, 'filename.png', SINGLE_BLOB) AS blobtable(image)

answered Jan 6, 2013 at 16:02

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TomTom Over a year ago

BUT: That is bad practice noramlly as it assumes the file is reachable FROM THE SERVER - not whatever application you run it in. Better: load it in your application and then move it via prameterized query.

Hamlet Hakobyan Over a year ago

@TomTom OK, I fully agree with you, but, maybe, OP wants load files to DB by SSMS in test purpose.

TomTom Over a year ago

Well, maybe he likes eating Pizza. Irrelevant. SSMS etc. is something he is respsonsible for mentioning, not me for assuming. SImple like that. I assume the 95% of all use cases: an application writing data to the Server, not the 5% edge case.

Hamlet Hakobyan Over a year ago

OK. But this 5% also have the right to life. Question is very concrete and not about best practice and design. And i gave concrete answer.

TomTom Over a year ago

Yes, and I expect those fringe cases to be named. Not someone making gross statements because somewhere in some cases it makes sense. your approach in normal applications is a reason to send you to HR to talk about incompetence. This answer should not be in a general question.

|

Collectives™ on Stack Overflow

How to insert binary file into a binary SQL column?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related