SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing datatype of a column


Changing datatype of a column

Author
Message
Rao.V
Rao.V
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 691
Hi,

Can anyone provide me query which helps in changing the datatype of an existing column of a table.Assume the old column is having data already.

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88427 Visits: 45284
ALTER TABLE <Table name> ALTER COLUMN <Column name> <New data type>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Q -631159
Q -631159
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 165
Vinu,

The command Gail provided works as long as SQL Server can do an implicit conversion. What is your datatype and what dataype are you wishing to change it to?

Q

Please take a number. Now serving emergency 1,203,894
gan-685682
gan-685682
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 215
if there's no conversion, can try from MS Enterprise Manager(same for MSSQL 2005)? go to design mode, select the column want to change, you will find the "Save chnages to script" is enabled, copy this and run for other server.

here are the sample script generated:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.titles
DROP CONSTRAINT FK__titles__pub_id__014935CB
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.titles
DROP CONSTRAINT DF__titles__type__00551192
GO
ALTER TABLE dbo.titles
DROP CONSTRAINT DF__titles__pubdate__023D5A04
GO
CREATE TABLE dbo.Tmp_titles
(
title_id tid NOT NULL,
title varchar(80) NOT NULL,
type char(12) NOT NULL,
pub_id char(4) NULL,
price money NULL,
advance money NULL,
royalty int NULL,
ytd_sales bigint NULL,
notes varchar(200) NULL,
pubdate datetime NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_titles ADD CONSTRAINT
DF__titles__type__00551192 DEFAULT ('UNDECIDED') FOR type
GO
ALTER TABLE dbo.Tmp_titles ADD CONSTRAINT
DF__titles__pubdate__023D5A04 DEFAULT (getdate()) FOR pubdate
GO
IF EXISTS(SELECT * FROM dbo.titles)
EXEC('INSERT INTO dbo.Tmp_titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
SELECT title_id, title, type, pub_id, price, advance, royalty, CONVERT(bigint, ytd_sales), notes, pubdate FROM dbo.titles (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.titleauthor
DROP CONSTRAINT FK__titleauth__title__060DEAE8
GO
ALTER TABLE dbo.sales
DROP CONSTRAINT FK__sales__title_id__0BC6C43E
GO
ALTER TABLE dbo.roysched
DROP CONSTRAINT FK__roysched__title___0DAF0CB0
GO
DROP TABLE dbo.titles
GO
EXECUTE sp_rename N'dbo.Tmp_titles', N'titles', 'OBJECT'
GO
ALTER TABLE dbo.titles ADD CONSTRAINT
UPKCL_titleidind PRIMARY KEY CLUSTERED
(
title_id
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX titleind ON dbo.titles
(
title
) ON [PRIMARY]
GO
ALTER TABLE dbo.titles WITH NOCHECK ADD CONSTRAINT
FK__titles__pub_id__014935CB FOREIGN KEY
(
pub_id
) REFERENCES dbo.publishers
(
pub_id
)
GO
GRANT REFERENCES ON dbo.titles TO guest AS dbo
GRANT SELECT ON dbo.titles TO guest AS dbo
GRANT UPDATE ON dbo.titles TO guest AS dbo
GRANT INSERT ON dbo.titles TO guest AS dbo
GRANT DELETE ON dbo.titles TO guest AS dbo
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.roysched WITH NOCHECK ADD CONSTRAINT
FK__roysched__title___0DAF0CB0 FOREIGN KEY
(
title_id
) REFERENCES dbo.titles
(
title_id
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.sales WITH NOCHECK ADD CONSTRAINT
FK__sales__title_id__0BC6C43E FOREIGN KEY
(
title_id
) REFERENCES dbo.titles
(
title_id
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.titleauthor WITH NOCHECK ADD CONSTRAINT
FK__titleauth__title__060DEAE8 FOREIGN KEY
(
title_id
) REFERENCES dbo.titles
(
title_id
)
GO
COMMIT


jaypee_s
jaypee_s
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 437
You can use like this, with in a transcation.

if object_id('Table Name') is Not Null
Begin
Drop Table Table NameEnd
Go

if object_id(column nmae) is Not Null
Begin
alter table tablename
alter column columnname
Go

in case of conversion... if we need to updated the column with empty or null values and then you can proceed further..
Rao.V
Rao.V
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 691
Hi Q,

My current column is having 'text datatype and i want it to change to 'datetime' datatype.....
jaypee_s
jaypee_s
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 437
hi

First change the datatype as varchar and datime.. it will work...

create table #a
(a text
)

alter table #a
alter column a varchar(10)


alter table #a
alter column a datetime
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88427 Visits: 45284
Varchar(25) at minimum, or you risk losing part or all of the time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Q -631159
Q -631159
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 165
vinu (5/11/2008)
Hi Q,

My current column is having 'text datatype and i want it to change to 'datetime' datatype.....


vinu,

Use the script that Gail posted twice.

ALTER TABLE tablename ALTER COLUMN columnname varchar(max)

ALTER TABLE tablename ALTER COLUMN columnname datetime


this will get you to a datetime from text. text will not convert to datetime directly. .

Q

Please take a number. Now serving emergency 1,203,894
Mike Levan
Mike Levan
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1407 Visits: 1893
How about changing INt to varchar(5), where it has millinons of records.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search