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 numberObj_id- unique number of the object to which the image belongsBody- encoded image (varbinarydata 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.