Bulk with Concat (Upload all images from a folder)

Question

I'm having a little bit of trouble with the function Bulk, I'm not sure how to do it. So, I need to upload all the images that I have in one folder into one table, I can upload 1 image with the next code:

Insert into ImageTest(ID, Imagen)
SELECT 'SQL Server Image', *
FROM OPENROWSET (BULK N'C:\Users\G11904\Pictures\Students\0001.PNG', SINGLE_BLOB) image;

ImageTest only has 2 columns, ID which is an NVARCHAR. The ID is going to save the name of the image. Imagen is an Varbinary, which is where I'm going to store my image.

I have ten images on my folder right now (Later on I will have like a thousand images).

My folder where I have all my images:

So I try the next code to try to store all the images:

DECLARE @cnt INT = 0;
DECLARE @cntImage INT = 1;
WHILE @cnt < 3000
BEGIN
Insert into ImageTest(ID, Imagen)
SELECT 'SQL Server Image', *
FROM OPENROWSET (BULK CONCAT(N'C:\Users\G11904\Pictures\Students\000', @cntImage, '.PNG'), SINGLE_BLOB) image; 
SET @cntImage = @cntImage + 1
END

But I'm getting this error:

Incorrect syntax near CONCAT.

Can someone help to try and solve this issue? I'm not sure what I'm doing wrong.

I am sure you can not concat openrowset query, u have to use dynamic sql — Ven, Dec 08 '17 at 18:36
Similar question posted here https://stackoverflow.com/questions/6621579/t-sql-issue-with-string-concat, — Ven, Dec 08 '17 at 18:38

lucky · Accepted Answer · 2017-12-08T18:41:27.067

Try to use dynamic query;

DECLARE @cnt INT = 0;
DECLARE @cntImage INT = 1;
declare @query nvarchar(1000)
declare @imagePath nvarchar(1000)
WHILE @cnt < 3000
BEGIN
set @imagePath  = CONCAT(N'C:\Users\G11904\Pictures\Students\000', @cntImage, '.PNG')
set @query = 
    'Insert into ImageTest(ID, Imagen)
    SELECT ''SQL Server Image'', *
    FROM OPENROWSET (BULK '''+@imagePath+''', SINGLE_BLOB) image; '
    print @query
    exec sp_executesql @query
    SET @cntImage = @cntImage + 1
END

OPENROWSET expects string. You can't pass a variable.

Bulk with Concat (Upload all images from a folder)

1 Answers1