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
Go to Top of Page

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
Go to Top of Page

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


Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -