adding column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

adding column

Author

Topic

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-01-29 : 08:53:03

Hi, I have to add a column to an existing table. I need to do this in an .sql for migration purpose.

How can I achieve to add a column and still keep the data of the table. My column is not nullable.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-29 : 09:24:34

[code]
use yourDataBase
go
IF EXISTS (SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=N'yourTable' AND COLUMN_NAME = N'your_migration_columnname')
ALTER TABLE yourTable DROP COLUMN your_migration_columnname
go
alter table yourTable add your_migration_columnname int not null default yourDefaultValue
go
[/code]
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-29 : 09:28:18

Or make it later not nullable...


ALTER TABLE yourTable ADD yourNewCol varchar(20)
UPDATE yourTable SET yourNewcol = somethingYouWant
ALTER TABLE yourTable ALTER COLUMN yourNewCol varchar(20) NOT NULL

btw. this is not a thread for the script library...
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-29 : 09:34:13

moved from script library.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-01-29 : 09:45:39

what is script library and transact-sql?

thanks for the answers

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 10:10:39

script library is designed for posting your scripts and other one is for T-SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources