0

How can we load a .png image into a SQL Server table?

I tried this but it didn't work:

INSERT INTO tableName(state, image) 
VALUES ('Ready', 
        (SELECT * FROM OPENROWSET (BULK 'imagePath'), SINGLE_BLOB) As image));
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nana
  • 1
  • 1
  • 1
  • Does this answer your question? [Insert Picture into SQL Server 2005 Image Field using only SQL](https://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql) – eshirvana Oct 12 '20 at 16:04
  • also avoid image datetype , this datatype is not supported ianymore :IMPORTANT! 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.https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15 – eshirvana Oct 12 '20 at 16:07
  • Also note that if the file is on your machine and the SQL Server remote, you'll need to specify a UNC path and SQL Server will need access to the share/file. – Dan Guzman Oct 13 '20 at 03:24

4 Answers4

0

Here is an example to save Image file in a SQL Server table, first of all we need to have a column that can handle Image files.

VARBINARY is data type that we can use to store Images or any other type of files. Let's create the table with Varbinary Datatype and then import the image by using below script.

CREATE TABLE dbo.TableName 
(ID INT Identity(1, 1)
 ,NAME VARCHAR(100)
 ,Image VARBINARY(MAX))
GO

--Import Image to SQL Server
INSERT INTO dbo.TableName 
(NAME
 ,Image
 )
SELECT 'Capture1.PNG'
 ,BulkColumn
FROM Openrowset(BULK 'C:\MyImage.PNG', Single_Blob) AS Image
CR241
  • 2,293
  • 1
  • 12
  • 30
  • Thank you CR241 for your reply. When trying this I have an error message: mass loading impossible the file does not exist or you do not have the rights to access the file – nana Oct 13 '20 at 08:20
0

The image path must be accessible by the SQL server. The SQL server can access files within its server folder. Locate the server folder. The folder name starts with MSSQLXX.MyDatabaseServer. If you had not changed the default installation path, you should be able to find it in "C:\Program Files\Microsoft SQL Server" Place your image file in "C:\Program Files\Microsoft SQL Server\MSSQLXX.MyDatabaseServer\MSSQL".

Specify the image path in sql like this -> FROM Openrowset(BULK 'C:\Program Files\Microsoft SQL Server\MSSQLXX.MyDatabaseServer\MSSQL\MyImage.PNG', Single_Blob) AS Image

If you still encounter error message "file does not exist or you do not have the rights to access the file", try placing your image file in the DATA folder where your primary Data File is stored.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Lunar
  • 1
0
CREATE TABLE dbo.ImageStore
(ID INT Identity(1, 1)
,[NAME] VARCHAR(100)
,[Image] VARBINARY(MAX))
GO

--Insert image to SQL server table using bulk import. 
INSERT INTO dbo.ImageStore 
([NAME]
,[Image]
 )
SELECT 'Error_image_1.PNG'
 ,BulkColumn
 FROM Openrowset(BULK 'C:\Issue_dataload_two Graphs.PNG', Single_Blob) AS Image

 select * from [dbo].[ImageStore]

This will store the image in binary format in your database. This works for me.

Gudwlk
  • 1,177
  • 11
  • 11
0

You can also paste the image data in as a hex string. This is useful if you don't have access to a file system on the SQL server.

Open the binary image file (e.g. jpeg) in a hex editor such as HxD and view the file as Hexadecimal. Copy the hex and paste into a query like this - you'll have to do a "find / replace" to remove some whitepace.

drop table if exists #img

create table #img
( icon varbinary(max))

insert into #img(icon)
select 
icon=convert(varbinary(max),'0x'+
'FFD8FFE000104A46494600010101004800480000FFDB00430006040506050406060506070706080A100A0A09090A140E0F0C1017141818171416161A1D251F1A1B231C1616202C20232627292A29191F2D302D283025282928FFC0000B080033003201011100FFC4001A000100030003000000000000000000000006000405030708FFC4002E1000020201030303030304030000000000010203040506111200132107223114153223335108161741244271FFDA0008010100003F00F54F46ADEA59ECDA9A9699C5CB95B30BB472D895CD6A513A9D8AB4E558B9DC3A911249C5D78BF0F9EAAC3A73534F24F364F5CDE8E4770638B158EAB0411A0551B01324EE49218925FF00EDB0036EB97FB6B3717EA41AE33724ABEE44B5568BC2CC3E03AA57462BFC8575246FB329F22A36A4CE69AA162CEB9C7D6931F5FB924996C3079238E2524879ABB6F2C7EDE1FB66600F32C5146FD35EA74435C59BB93B55B4A61A6BD52CE410C9772549D03E36A83E5BDDE449290D1C640DC7EA38FDA20AAA95A0A7561AB4E18A0AD022C7145120548D146C1540F000000007595A7355E0353497934F6628E49A8BAC760D5984810B2865F23C1041F91B8DC30DF756026AFD5584D1D866CAEA5C845428ABAC7DC70CC59DBE155541663F27600F804FC027AD5A9660B9561B54E68A7AD3A2C914B1386491186E1948F04104104742B035A9685D509A7E9C32C184CDBC9631B1A23BC55AD2AB3CF003F8C51B2812C683C72163E0705E9D744340CAF91C9EADCBC904A91CF967A956498A97686B2240CA362488C4E965954EDF9B36C399DD7F5E65FE91346EA8D2D9FD5A35053BD8BACA90C06B58ABB259943BED2472FC30401C7B0956EE83BF81BB0FEAD74EE63527A65521C063ACE46C56C9C562486B2192411F6E54DC20F2DEE75F0013B127E0123B03D28A36B1BE99695A790367EAE1C6575912CA049226EDAFE9950ABB70FC76237D946E49DC99EA9FE97A7D9BBCBFBB8C87EEB083F8B4B558588D5BF952F12860362413B107C855D0AF4F27B4BA7F3F5E1A7C6FD3CD651522B3288D6467B52CD192579322B2CA87765DF62182952A5AAFA45AD731ADA1D4536670D5B11F6DC9BE356BC768CF22C91A27755C850A7663E194EC41DB6F6F277FD4E8AFAA5A9E4D19A1327A8615ACED47B4E63B05C2CAA6545280A0243302554EC4062A5BDA0F595AC2F65323E876A8B59EC47D9B252616FF00768FD4AD8ED6D1C817F517C36EA0378F8DF6FF005D3AA95D2AD586BC46568E1458D4CB2348E401B0E4EC4B31FE49249F927A1F39ABA57D417B966C76286A8ED57DE59C88D721129540AA491CA68871DC051BD55079348A3AC5F502A5DD1BAA0FA8782A72DDAC6AFD36A1A113A45CEAC6AD20B6BBB2869A30BC406E4595B88E1E4F4EB4CEA2C3EA8C54792D3D91AD90A4FB0EE40E1B8B150DC587CAB00C3756008DFC81D5BCA64A8E2284B7B2B76B51A516DDCB166558A34DC803766200DC903FF48EBAAB0D77FCC1AAB1B9886B594D03829BEAA9493C7D87C864A37654997E58C08BB91F86EEDB306E255556AF6FBF6AAC2698AD3598FE966833991648BD9D889D8C11990A9019EC468DC46C4A432F91E3935EB3F3F86C76A1C35BC4E6AA45731D693B734320F0C3E4791E41040208D8820104103A2B5B319BD2121ADAC5E5CAE265B4E2B67608541AD0F12FFF0039102AC61762BDE41C3600B88FE5B8AC7A7DE9E6B29A9EA1AD8CC6D9944CF661C9E26C181A4979EED2196BB2F360EBF9124820EDB127AA9FE1DF4DB1567EF3770559BE8E1F74D92B93588638923E039ACCEC9C55000390D9428DB6D86DB736AC9F312415F43568B2B1CAE526CB3B1142AA716DA4571B7D490C38F6E23E0821DE3F07ADAD3D82AB8286D0AD2599EC5C9BEA6DD9B3299249E6E0A85CFF00A5F6A28E281514001540F1D6AF53A9D15BBE9E694B7956CA7D92B55CA37709BD44B54B05A460CEDDD88AB723C762DBEFB338DF67606A47E9668AFAF7BD6F4FD6C95D93973B19477BD23EE107B9A76727611205DFF11CB8EDC9B76BD4EA75FFD9'
, 1)

select * from #img
James Mc
  • 549
  • 6
  • 10