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.

Solution 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

Solution 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.

Solution 3

How about you create a NewTable with the varbinary, then copy the data from the OldTable into it?

INSERT INTO [dbo].[NewTable] ([id], [data])
            SELECT  [id], [image] FROM  [dbo].[OldTable]

Solution 4

First of all from BOL:

image: Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

The image data type is essentially an alias for varbinary (2GB), so converting it to a varbinary(max) should not lead to data loss.

But to be sure:

1. back up your existing data
2. add a new field (varbinary(max))
3. copy data from old field to new field
4. swap the fields with sp_rename
5. test
6. after successful test, drop the old column

View more solutions