Save as script - 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)

Save as script

Author

Topic

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-01-28 : 11:53:48

In Sql Server 2005, when you modify a table for example, you can save those changes into one .sql file.

I added one column to a table and save the change into a .sql

To my surprise, it generate a .sql with like 100 lines ... i was expecting to generate a single line something along the way of Alter table ADD [column] type.

But it was doing like dropping all contrainst, create a temp table, insert data into that temp table, drop the original table, rename the temp table to the orginal table and re-create the constraint ....

why???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:55:56

were you trying to turn an already existing column to be of identity type?

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-01-28 : 14:14:08

I just added a new column into a table, the column is not nullable.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-29 : 00:22:30

just sample data for example
create table sampletable
( empid int identity(1,1) primary key,
empname varchar(32),
empsal decimal(18,3)
)
after creation of table
---if u want to add column use this
alter table sampletable add empjoining datetime not null

-- if u want to add column with default value use this
alter table sampletable add empjoining datetime not null constraint df_k default (getdate())

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-29 : 00:26:13

If u have a data in the table, u can't add the column with not null
only table accepts nullable column

if already created Table,

then alter table tablename add column datatype

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-29 : 00:32:25

quote:
Originally posted by Nageswar9

If u have a data in the table, u can't add the column with not null
only table accepts nullable column

if already created Table,

then alter table tablename add column datatype

Hi Nageswar9,

Eventhough your table contains data you can add a column having not null constraint if it also has default constraint

ex:

ALTER TABLE yourtable ADD NewColumn datatype NOT NULL DEFAULT <DefaultValue> WITH VALUES

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-29 : 00:36:43

quote:
Originally posted by raky

quote:
Originally posted by Nageswar9

If u have a data in the table, u can't add the column with not null
only table accepts nullable column

if already created Table,

then alter table tablename add column datatype

Hi Nageswar9,

Eventhough your table contains data you can add a column having not null constraint if it also has default constraint

ex:

ALTER TABLE yourtable ADD NewColumn datatype NOT NULL DEFAULT <DefaultValue> WITH VALUES

Hi Raky,
I am not Speak About Default Key , I just tell the How to add column in the table in already exsisting data.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-29 : 00:39:27

quote:
Originally posted by Nageswar9

quote:
Originally posted by raky

quote:
Originally posted by Nageswar9

If u have a data in the table, u can't add the column with not null
only table accepts nullable column

if already created Table,

then alter table tablename add column datatype

Hi Nageswar9,

Eventhough your table contains data you can add a column having not null constraint if it also has default constraint

ex:

ALTER TABLE yourtable ADD NewColumn datatype NOT NULL DEFAULT <DefaultValue> WITH VALUES

Hi Raky,
I am not Speak About Default Key , I just tell the How to add column in the table in already exsisting data.

Yes Nageswar9, Iam Just telling that It is not impossible to add a column with not null constraint to a table which is already having data.We can do it by keeping some default constraint to it. That is what iam saying.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-29 : 00:44:20

Ok Raky, Today I learnt one more point about defult constraint...
Thank u

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-29 : 00:45:26

quote:
Originally posted by Nageswar9

Ok Raky, Today I learnt one more point about defult constraint...
Thank u

Welcome...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources