SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Changes in DataBase Structure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunny_10
Yak Posting Veteran

65 Posts

Posted - 04/02/2013 :  01:42:23  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/02/2013 :  03:38:29  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 04/02/2013 :  07:39:31  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/02/2013 :  11:55:08  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
5072 Posts

Posted - 04/02/2013 :  12:12:46  Show Profile  Visit russell's Homepage  Reply with Quote
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"

Edited by - russell on 04/02/2013 12:15:55
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/02/2013 :  12:53:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000