Changes in DataBase Structure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Changes in DataBase Structure

Author

Topic

sunny_10
Yak Posting Veteran

72 Posts

Posted - 2013-04-02 : 01:42:23

Hi

I want to change Data Type from decimal to int . Table has some records . It is not allowing me to do changes .

Thanks

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-02 : 03:38:29

If it already has decimal data, there is no way to convert that to int directly. You could select it into a temp table, and round to the nearest int, then recreate the table with an int colmn, and insert from the temp table into the new table.

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-02 : 07:39:31

quote:
Originally posted by chadmat

If it already has decimal data, there is no way to convert that to int directly. You could select it into a temp table, and round to the nearest int, then recreate the table with an int colmn, and insert from the temp table into the new table.

-Chad

Not true
see this illustration


create table #tab 
(
col decimal(8,2)
)
--insert value
insert #tab
values (12.34),(19.30),(20),(23)

--check the contents
select * from #tab

--now change datatype
alter table #tab alter column col int

--check the contents
select * from #tab

drop table #tab



output
------------------------------------
col
--------------
12.34
19.30
20.00
23.00



col
-----------
12
19
20
23

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-02 : 11:55:08

I didn't realize you could do that. Figured the alter would fail.

Thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by chadmat

If it already has decimal data, there is no way to convert that to int directly. You could select it into a temp table, and round to the nearest int, then recreate the table with an int colmn, and insert from the temp table into the new table.

-Chad

Not true
see this illustration

create table #tab
(
col decimal(8,2)
)
--insert value
insert #tab
values (12.34),(19.30),(20),(23)

--check the contents
select * from #tab

--now change datatype
alter table #tab alter column col int

--check the contents
select * from #tab

drop table #tab

output
------------------------------------
col
--------------
12.34
19.30
20.00
23.00

col
-----------
12
19
20
23

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-02 : 12:12:46

quote:
Originally posted by sunny_10

Hi

I want to change Data Type from decimal to int . Table has some records . It is not allowing me to do changes .

Thanks

What is the error message you're getting? Are you using the GUI or T-SQL? If the GUI, go to TOOLS/OPTIONS/ expand DESIGNERS, Table and Database Designers. Uncheck the option that says "Prevent saving changes that require table re-creation"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-02 : 12:53:30

quote:
Originally posted by chadmat

I didn't realize you could do that. Figured the alter would fail.

Thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by chadmat

If it already has decimal data, there is no way to convert that to int directly. You could select it into a temp table, and round to the nearest int, then recreate the table with an int colmn, and insert from the temp table into the new table.

-Chad

Not true
see this illustration

create table #tab
(
col decimal(8,2)
)
--insert value
insert #tab
values (12.34),(19.30),(20),(23)

--check the contents
select * from #tab

--now change datatype
alter table #tab alter column col int

--check the contents
select * from #tab

drop table #tab

output
------------------------------------
col
--------------
12.34
19.30
20.00
23.00

col
-----------
12
19
20
23

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

it wont unless the datatypes of values inside are mutually not compatible with changed type.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources