How can I create a script that updates a table with new fields without
losing the data in the table (i.e., without dropping and recreating the
table)?

Thanks.

Use an ALTER TABLE statement:

ALTER TABLE your_table_name ADD new_column_name INTEGER NULL

See Books Online for the full syntax.

--
David Portas
SQL Server MVP
--

Thanks. It would be nice if EM provided a way to automatically script that
for you, the way it does for adding entire tables. But I guess some things
need to be done by hand.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1117621367.921448.203030@g44g2000cwa.googlegr oups.com...
> Use an ALTER TABLE statement:
>
> ALTER TABLE your_table_name ADD new_column_name INTEGER NULL
>
> See Books Online for the full syntax.
>
> --
> David Portas
> SQL Server MVP
> --
>

EM will generate the script for you. In the Design Table screen, add a
column and then click the "Save change script" button (third on the
toolbar). If you insert a column in the middle of the structure
however, it will generate a script to recreate the entire table,
because there is no way to fix absolute column order with an ALTER
TABLE statement.

It pays to make table mods through Query Analyzer rather than the EM
interface. TSQL is more powerful and gives you more control.

--
David Portas
SQL Server MVP
--

Great! Thanks for the info.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1117705690.710697.59860@g44g2000cwa.googlegro ups.com...
> EM will generate the script for you. In the Design Table screen, add a
> column and then click the "Save change script" button (third on the
> toolbar). If you insert a column in the middle of the structure
> however, it will generate a script to recreate the entire table,
> because there is no way to fix absolute column order with an ALTER
> TABLE statement.
>
> It pays to make table mods through Query Analyzer rather than the EM
> interface. TSQL is more powerful and gives you more control.
>
> --
> David Portas
> SQL Server MVP
> --
>

Neil (nospam@nospam.net) writes:
> Thanks. It would be nice if EM provided a way to automatically script that
> for you, the way it does for adding entire tables. But I guess some things
> need to be done by hand.

Count yourself lucky for not having found it, and damn the day David
told you that you could do it.

To wit, yes, there is a function for this in Enterprise Manager, but, no,
you should not use it. There are several serious flaws with it, and you
could end up messing up your database.

As David mentioned, EM will recreate the table if you insert a column in
the middle. What he didn't say is that EM will do this also if you change
or drop a column, despite there is an ALTER TABLE syntax for this.
Basically, EM assumes that it's talking to an 6.5 database.

Now, recreating table is necessarily not bad. In fact, this can be
preferable over an ALTER TABLE in some situations, even if ALTER TABLE
is available. But recreating the table, requires you to have some sort
of recovery strategy, if the operation fails half-way. EM applies a
transaction scope, and as long as you run the thing directly from EM,
it's safe, but if you save the script, the script is not safe. Furthermore,
EM's transaction scope is wacko.

Another horror story with EM is that you can start doing a change to
table, then abandon that table and close it. Yet, you may find this
change performed anyway when you save, because this table was referred
by some other table you saved!

So don't use Enterprise Manager to modify tables. The same applies to
the Modify Table function in the new SQL Server Management Studio.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp