Generating Insert Statements from Table with Image

Transact-SQL
1

I have a need to dynamically generate insert statements for a table containing an image column. I have tried converting that column to a hexdecimal string first but that does not appear to work. Has anyone had success doing this?

2

Could you use BCP to export / import the table instead? (using NATIVE mode to preserve the binary data)

3

Did you try SQL scripting? You can set the options in SSMS so that SQL generates data as well as schema. I'm not sure if you can do just data, but I know you can do data along with the schema at least.

4

If you really need insert statements, ie varbinary as text, you will need to look at some sort of encoding.

An example of Base64 is here:

With a table like:

CREATE TABLE YourTable
(
    YourID int NOT NULL
    ,YourImage varbinary(MAX) NOT NULL
);

Something like the following should get you started:

SELECT 'UNION ALL SELECT ' + CAST(Y.YourID AS varchar(20)) + ', '
    + 'CAST(''' + A.YourImage64 + ''' as XML ).value(''.'',''varbinary(MAX)'')'
FROM YourTable Y
    CROSS APPLY
    (
        SELECT CAST(Y.YourImage AS varbinary(MAX)) FOR XML PATH(''), BINARY BASE64
    ) A (YourImage64);