Incorrect insertion of data into the table

60 views Asked by At

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.

0

There are 0 answers