ALTER TABLE from sqlcmd script

Giganews Newsgroups
Subject: ALTER TABLE from sqlcmd script
Posted by:  Jeff_in_MD (jfowl…
Date: 10 Aug 2006


I'm trying to add a column to a table, then update that column with a
query.  This is all within a single batch.  Sqlcmd gives me an error on
the update, saying "invalid column xxx", because it doesn't know the
column got added.  We used to get around this in "osql" by using the
EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)
not null default ' '")

However, it looks like sqlcmd actually checks each query within the
script before it starts running, and throws the error because the field
isn't there at the time.

If need be I can just do a SELECT INTO and add the column there, but
it's a pain in the butt and I'm moving a LOT of data just to do what I
want.  And no, I can't go back to where the table is created and add
the column.  Does anyone have any suggestions?  TIA!

- Jeff