Convert table column data type from image to varbinary

Question

I have a table like:

create table tbl ( 
  id int,
  data image
)

It's found that the column data have very small size, which can be stored in varbinary(200)

So the new table would be,

create table tbl ( 
    id int,
    data varbinary(200)
)

How can I migrate this table to new design without loosing the data in it.

Answer 1

Just do two separate ALTER TABLEs, since you can only convert image to varbinary(max), but you can, afterwards, change its length:

create table tbl ( 
  id int,
  data image
)
go
insert into tbl(id,data) values
(1,0x0101010101),
(2,0x0204081632)
go
alter table tbl alter column data varbinary(max)
go
alter table tbl alter column data varbinary(200)
go
select * from tbl

Result:

id          data
----------- ---------------
1           0x0101010101
2           0x0204081632

Answer 2

You can use this ALTER statement to convert existing column IMAGE to VARBINARY(MAX). Refer Here

ALTER Table tbl ALTER COLUMN DATA VARBINARY(MAX)

After this conversion, you are surely, get your data backout.

NOTE:- Don't forgot to take backup before execution.

The IMAGE datatype has been deprecated in future version SQL SERVER, and needs to be converted to VARBINARY(MAX) wherever possible.