3D image of an insert from a relative path

Question

I wonder if some can help me with this little problem. I have the following insert statement:

insert into symbol (sy_id, sy_fg_color, sy_bg_color, sy_icon) select 302, 0, 16245177, sy_icon = (select * from openrowset(bulk 'K:\mypath\icons\myicon.png', single_blob) as image) 

Is there any way to make the path relative? I use TFS to deploy the database, so if this cannot be done relative with T-SQL, maybe this can be done with a little help from deploying TFS / Visual Studio?

Answer 1

You can insert varbinary(max) into the field using T-SQL using the OPENROWSET OPENROWSET .

 INSERT dbo.tblPhotos ( LargePhoto ) SELECT tblPhotos.* FROM OPENROWSET (BULK 'c:\images\image*.jpg', SINGLE_BLOB) ThumbnailPhoto 

Please note that the file path in this case refers to the target SQL server and not to your client executing this command.

Essentially, there are two ways to CHOOSE BLOB with TSQL:

 SELECT * FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a 

As well as:

 SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a 

Then you can use it for INSERT by doing INSERT SELECT ... or UPDATE SELECT ...

More details here.