I am trying to do conversion from varbinary(max)->varchar(max)->varbinary(max), this is truncating the varbinary data, how can I do this conversion without loosing the original information in the conversion process?
Below is the situation I am seeing this issue (to give you a better idea on what I am trying to accomplish).
As part of archival solution I am copying data from multiple tables to a single table and will be able to migrate back to the original table when needed, this is working fine except for blob (varbinary) datatypes.
The arichival table has table name as the first column and have columns col_01,col_02,....col_100 (maxmium 100 columns ca be stored) all these columns are of type varchar(max).
I created two stored procs which will take table name as parameter.
stored proc #1 will select all rows (convert each column to vatchar(max) ) from the table (identified by table name parameter) and insert the tows into the archieve table (first column will be table name so I know what rows to retrieve when I restore the records). This is working great no issues on sql execution but I loose all varbinary-blob information during conversion to varchar(max) data type - see example query below.
insert into data_archieve_table (table_name,col_01,col_02,col_03,col_04,....)
select convert(varchar(max),customer_id) as col_01,convert(varchar(max),customer_first_name) as col_02,convert(varchar(max),customer_last_name) as col_03,convert(varchar(max),customer_dob_dt) as col_04,.... from customers
Stored proc #2 will select all rows with first column = @table_name parameter for the proc to identify all rows I need to retrieve and based on the datatype of column (I identify this by looking at indormation_schema.columns) and use convert(datatype,col_xx) - See example query below
This query also works great. But now when I inspect any table which has varbinary data type (most of them are varbinary(max) which we use to store blobs) I can see that the data is truncated, this is not same as the original blob data, something is wrong with the conversion varbinary(max)->varchar(max)->varbinary(max)
insert into customers(customer_id,customer_first_name,customer_last_name,customer_dob_dt,....)
select convert(bigint,col_01) as customer_id,convert(varchar(max),col_02) as customer_first_name,convert(varchar(max),col_03) as customer_last_name,convert(datetime,col_04) as customer_dob_dt,.... from data_archieve_table where table_name = 'customers'
