Changing datatype of a column

  • 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

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

  • 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

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

  • Hi Q,

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

  • 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

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

  • 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