I am trying to insert file through sql. I use following query.
INSERT INTO [dbo].[Attachments]
(FileName, FileBinary)
SELECT 'non-date-in-sql-server-column',
BulkColumn FROM OPENROWSET(
Bulk 'C:\Users\Pictures\Picture.JPG', SINGLE_BLOB) AS BLOB
Its working fine.
I want to write the procedure that take dynamic path. Its giving me error that I cannot take Filebinary in addin. Which is datatype varbinary. What is the best way to do ?
I have done following but its not taking properly binary value.
DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = 'SELECT ' + '''' +@Filename +'''' + ' AS Name,' + 'FileBinary
FROM OPENROWSET(BULK N''' + @ImagePath + ''',SINGLE_BLOB) AS FileBinary(FileBinary);'
Insert Into Attachments
(
ApplicantID,
FileName,
FileBinary
)
Values
(
@ApplicantID ,
@FileName,
Convert(varbinary(max),@SQLString)
)