Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing datatype of a column Expand / Collapse
Author
Message
Posted Monday, May 5, 2008 12:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:11 AM
Points: 339, Visits: 581
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
Post #494798
Posted Monday, May 5, 2008 1:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
ALTER TABLE <Table name> ALTER COLUMN <Column name> <New data type>


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #494802
Posted Tuesday, May 6, 2008 8:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, 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

Post #495673
Posted Friday, May 9, 2008 10:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:03 AM
Points: 79, Visits: 211
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

Post #498318
Posted Sunday, May 11, 2008 11:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:11 AM
Points: 127, Visits: 416

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..



Post #498552
Posted Sunday, May 11, 2008 11:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:11 AM
Points: 339, Visits: 581
Hi Q,

My current column is having 'text datatype and i want it to change to 'datetime' datatype.....
Post #498555
Posted Monday, May 12, 2008 1:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:11 AM
Points: 127, Visits: 416
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
Post #498577
Posted Monday, May 12, 2008 1:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
Varchar(25) at minimum, or you risk losing part or all of the time.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #498584
Posted Wednesday, May 14, 2008 6:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2008 7:27 AM
Points: 239, 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

Post #500416
Posted Friday, October 31, 2008 1:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 3, 2010 2:42 PM
Points: 1,023, Visits: 1,893
How about changing INt to varchar(5), where it has millinons of records.
Post #595254
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse