Convert a BINARY stored as VARCHAR to BINARY

I do an INSERT SELECT from a table (source) where every column is of VARCHAR datatype.

One of the columns stores binary data like

'0003f80075177fe6'

The destination table, where I insert this, has the same column, but with proper data type of BINARY(16).

INSERT INTO destination
(
    column1,        --type of BINARY(16)
    ...
)
SELECT
    CONVERT(BINARY(16),[varchar_column_storing_binary_data]),   --'0003f80075177fe6'
FROM source
GO

When I insert it, then select the destination table, I got a different value from the BINARY16 column:

0x30303033663830303735313737666536

It does not really seems like the same value.

What should be the proper way to convert binary data stored as VARCHAR to BINARY column?

The result you get is because the string "0003f80075177fe6" (a VARCHAR value) is converted to code points, and these code points are served up as a binary value. Since you're probably using an ASCII-compatible collation, that means you get the ASCII code points: 0 is 48 (30 hex), f is 102 (66 hex) and so on. This explains the 30 30 30 33 66 38 30 30...

What you want to do instead is parse the string as a hexadecimal representation of the bytes (00 03 f8 00 75 71 77 fe 66). CONVERT accepts an extra "style" parameter that allows you to convert hexstrings:

SELECT CONVERT(BINARY(16), '0003f80075177fe6', 2)

Style 2 converts a hexstring to binary. (Style 1 does the same for strings that start with "0x", which is not the case here.)

Note that if there are less than 16 bytes (as in this case), the value is right-padded with zeroes (0x0003F80075177FE60000000000000000). If you need it left-padded instead, you have to do that yourself:

SELECT CONVERT(BINARY(16), RIGHT(REPLICATE('00', 16) + '0003f80075177fe6', 32), 2)

Finally, note that binary literals can be specified without conversion simply by prefixing them with "0x" and not using quotes: SELECT 0x0003f80075177fe6 will return a column of type BINARY(8). Not relevant for this query, but just for completeness.