Incorrect insertion of data into the table

Question

I have a table for storing images in a database (#TempImage). It contains the following columns:

Image_ID	Obj_id	Body	TYPE
1	22	0x89504E470D0A1A0A0000000D494844	image/png
2	23	0x89504E470D0A1A0A0000000D494844	image/png

• Image_id - unique image number
• Obj_id - unique number of the object to which the image belongs
• Body - encoded image (varbinary data type)
• Type - image type (filled in automatically)

I need to add a lot of images to this table.

I have a csv document (Image.csv) where information about obj_id and body. Information in body field looks like:

89504E470D0A1A0A0000000D4948445200000319000002340802000000FEC473F40001000049444154789CECFD5

I tried to do this queries:

CREATE TABLE #TempImageHexString
(
 ID int NOT NULL,
 BodyHexString varchar(MAX)
)
 
CREATE TABLE #TempImageHex
(
 ID int NOT NULL,
 Body varchar(MAX)
)
BULK INSERT #TempImageHexString
FROM 'D:\Image.csv'
WITH (
 ROWTERMINATOR = '\n',
 FIELDTERMINATOR = ';',
 FIRSTROW = 1)
 
INSERT INTO #TempImageHex (ID, Body)
 SELECT ID, CONVERT(varbinary(MAX), BodyHexString, 0)
 FROM #TempImageHexString
 
INSERT INTO #TempImage (FEATURE_TRANSACTION_ID, BODY) 
 SELECT ID, CONVERT(varbinary(MAX), Body, 0) 
 FROM #TempImageHex

When inserting into the #TempImage table, the following values appear in the Body column:

0x38393530344534373044304131413041303030303030304434393438343435323030303030333139303030303032333430383032303030303030464543343733463430303031303030303439343434313534373839434543464435393933363444393735314530414545453143433833434634333738434339313533363536363044353938353739323043383442304432364632344142433444423535394442464430313741443041333443364633324244463737443934374538304343443442413644343646

I don't know what is it, it's definitely not a .png. And also I want the Type column to be automatically filled with image/png values.

Please tell me what I'm wrong about and how to fix this error.

• Dale K: Is that string from your file supposed to be a varbinary value? if so it should start with 0x... if not, well that explains your error.
• AlwaysLearning: Seems you've read enough of the CAST and CONVERT (Transact-SQL) documentation to know that convert() can accept binary styles but it doesn't look like you've read it closely enough. Style 0 translates ASCII characters to binary bytes, so 8=>0x38, 9=>0x39, etc. Have you tried using any of the other binary styles? e.g.: CONVERT(varbinary(MAX), '0x'+BodyHexString, 1)
• Thom A: Are you really still using 2008? That version ran completely out of support over 4 years ago; it lacks a lot of functionality taken for granted now and is also a security vulnerability. You really need to get that finalised upgrade plan of yours implemented.
• Dale K: How would SQL server know what the image type is from a binary file?
• siggemannen: @AlwaysLearning is right, i think the OP got this convert style from previous question asked about this issue. I also think this is generally a bad idea to store images in the database. But at least if you're doing it, you might as well store file name so it's easy to figure out if it's png or tiff