May 5, 2008 at 12:55 am
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
May 5, 2008 at 1:17 am
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
May 6, 2008 at 8:26 am
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
May 9, 2008 at 10:41 pm
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
May 11, 2008 at 11:31 pm
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..
May 11, 2008 at 11:37 pm
Hi Q,
My current column is having 'text datatype and i want it to change to 'datetime' datatype.....
May 12, 2008 at 1:07 am
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
May 12, 2008 at 1:16 am
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
May 14, 2008 at 6:57 am
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
October 31, 2008 at 1:32 pm
How about changing INt to varchar(5), where it has millinons of records.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply