CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014alter-table
Praveen
Praveen

Reputation: 9335

Convert table column data type from image to varbinary

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.

Upvotes: 3

Views: 29203

Answers (4)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8487

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.

Upvotes: 5

Pred
Pred

Reputation: 9042

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

Upvotes: 1

jlee88my
jlee88my

Reputation: 3043

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]

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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

Upvotes: 7

Related Questions