How To Bulk Upload Images In Sql Table

Introduction

Sometimes we need to store information including photos in our database. For example, the photo of the product, the photo of the team members. But how tin we store images in SQL Server?

We could create an application in .NET or Java, but if we do non accept feel in those programming languages, nosotros could utilize SQL Server tools to do it.

In this new commodity, we will learn the following tips that will assistance us to work with images including how to:

insert ane epitome into SQL Server
store multiple files into a table
verify that the images were inserted

Requirements

SQL Server 2016 or later with SSMS installed. You can use older versions including SQL Server 2008.
SSDT 2015 installed, but earlier versions are immune.
SSRS installed

Getting started

Insert one prototype into SQL Server

We will offset learn how to load 1 single image into a table in SQL Server.

We will load a file named i.png that contains the image of your apprehensive writer into the tabular array myimages:

We will first create a table named myimages in SQL Server:

CREATE TABLE myimages ( id int , img varbinary ( max ) )

This tabular array will take an integer (int) id and the paradigm column named img. The data type that we are going to use to shop images is the varbinary(max).

We will now insert an image into the tabular array myimages:

INSERT INTO adventureworks . dbo . myimages values ( 1 , ( SELECT * FROM OPENROWSET ( Majority Northward 'C:\img\1.png' , SINGLE_BLOB ) as T1 ) )

The INSERT statement inserts the value i equally the id and then inserts the epitome named 1.png from the folder img in the c drive. I am assuming that you have an image in that path. We are using the OPENROWSET to admission remote data like OLE DB data sources.

We now know how to insert one single prototype into our database. Even so, how can we save all the files from a folder into our table?

The next section contains this answer.

How to shop multiple files into a tabular array

We accept a folder named img with 4 images. How can nosotros insert all of them into a SQL Server table:

We will use PowerShell this time to do this task. If yous have SSMS 17, y'all may need to read the next commodity to install PowerShell:

What is new in SSMS 17; PowerShell and DAX

PowerShell is very useful to deal with files, folders, services and the operative organization. That is why we will use this trounce to help usa in the task requested.

To simplify this task, nosotros will truncate the tabular array with the image inserted in the previous example:

In PowerShell go to the SQL Powershell:

We volition starting time open up PowerShell and go to the binder with the images using the change directory command (cd):

Nosotros volition shop all file names in the variable files:

Dir lists all the files and folders from the current path (c:\img). -Name will show the file names.

Now, nosotros will create a counter to insert the id into the table starting in the number 1:

We will make a loop and invoke the sqlcmd to insert each image into the SQL Server tabular array named myimages. We use a foreach statement. In this example, we have four files in our folder. That ways that nosotros will invoke the sqlcmd 4 times. The counter variable will be used to insert numeric values in the id column from 1 to 4. Note that in the INSERT INTO we are specifying the databasename.schema.tablename. By default, invoke-sqlcmd will be in the primary database.

foreach ( $ file in $ files )

{

invoke - sqlcmd "INSERT INTO adventureworks . dbo . myimages values ( $ counter , ( SELECT * FROM OPENROWSET ( BULK N 'C:\img\$file' , SINGLE_BLOB ) every bit T1 ) )"

$ counter ++

}

Annotation that we are concatenating the $file variable with the file names of each paradigm. $counter++ will be used to insert the value 1 in the column id and 2 in the next iteration, 3 in the next one and and so on.

OK, now we have iv images in our table. How tin can nosotros verify that the images were inserted successfully?

The reply is in the adjacent department.

How to verify that the images were inserted

This time, nosotros will use Reporting services and SSDT to verify that the images were inserted correctly. If yous do non have experience with reporting services, do not worry. This article is for you lot.

Open SSDT and create a New Project. In new projection use the Report Server Projection Wizard:

The Wizard will give you a Welcome message. Press next:

In the Select Information Source Window, printing the edit button to create a connection:

Specify the Server Name and the Database proper name and printing OK:

In Select Information Source, press Next:

In Design the Query step, press the Query Builder button to create the query with the data that we want in the report:

Press the add table icon:

Add the table "myimages" that we previously created

Check the columns of the tabular array. This volition generate a statement "select id, img from myimages":

Select the Tabular Report Type and press Next:

In Design the Table pace, printing Finish:

In completing the wizard, specify a name for the study and press Finish:

In the Report, go to Toolbox tab on the left:

Drag and driblet the image into the img cavalcade:

In the combo box select the image source, select Database. In use this field, select img. In the textbox Use this MIME blazon, select image/png:

In Size folio, select Display Original Size. This selection will permit to apply the original size of the image and press OK:

Press the previous tab to view the image:

Equally you can encounter, the four images were stored successfully into the myimages tabular array:

Conclusions

SQL Server allows storing files. In this article, we learned how to insert a single image file into a SQL Server tabular array using T-SQL. Nosotros as well learned how to copy multiple files from a folder into SQL Server in a tabular array.

Finally, we worked in SQL Server Reporting Services to view the images inserted. To do that we used the SSRS wizard.

If you take more questions, feel gratuitous to comment!

References

OPENROWSET (Transact-SQL)
What is SQL Server Reporting Services (SSRS)?
Add a Data-Leap Image (Report Builder and SSRS)

Author
Contempo Posts

Daniel Calbimonte is a Microsoft Virtually Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at It Academies and has over 13 years of feel working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server preparation materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte