Here is an overkill example. I have some comments interspersed:
set nocount on
if object_id('dbo.test') is not null drop table dbo.test
go
create table dbo.test(
id int not null
, vc varchar(10) not null
)
go
insert into dbo.test values (1, '3')
-- we will look at the layout of the table here
select COLUMN_NAME, DATA_TYPE, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'test'
order by ORDINAL_POSITION
go
-- attempt to convert column types
alter table dbo.test alter column id varchar(10) null
go
alter table dbo.test alter column vc int null
go
-- we will look at the NEW layout of the table here
select COLUMN_NAME, DATA_TYPE, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'test'
order by ORDINAL_POSITION
go
insert into dbo.test values ('a', 4)
go
-- this statement will fail because the data in the
-- column cannot implicitly be converted to a smallint
alter table dbo.test alter column id smallint not null
go
-- note nothing is changed from the previous layout
select COLUMN_NAME, DATA_TYPE, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'test'
order by ORDINAL_POSITION
go
-- clean up
if object_id('dbo.test') is not null drop table dbo.test
go
Tim Starling wrote:
I am trying to change the data type of a column in a ms sql table and
have followed the examples that I have but I keep getting a syntax error
– I’m probably overlooking something. Here is the SQL that I am using:
<cfquery datasource="aff" name="Dataadd">
ALTER TABLE affiliated
MODIFY Affiliate_bday int;
</cfquery>
The error I get is:
Incorrect syntax near 'Affiliate_bday’.
What is the proper syntax to change the data type for a column in MS
SQL? I need to change a column that was set up as varchar to be an integer.
Thanks,
Tim
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
