Column creation and use

Question

Hi,

I am fairly new to SQL, so not sure if this is just a SQL compiler thing or not. When I have used SQL before it's been through JAVA or PHP, never just straight SQL. I am using SQL Server 2005 and attempting to add a column to my table, and then populate it. Here is what I have now:

ALTER TABLE House DROP COLUMN CustomerType
ALTER TABLE House ADD CustomerType char(11)

UPDATE House
SET CustomerType = CASE
    WHEN ... THEN...
    WHEN ... THEN...
    ELSE "Other"
    END

However, when I try and compile this it errors since CustomerType in the UPDATE function hasn't been defined yet. Is there any way to run this so that it will compile and add the column and update it, or do I have to run it in multiple executes?

Thanks!

Answer 1

Try putting a GO statement after the second ALTER statement and before your UPDATE statement.

Answer 2

Use multiple files for each operation, or put "GO" between the steps:

ALTER TABLE House DROP COLUMN CustomerType
GO

ALTER TABLE House ADD CustomerType char(11)
GO

UPDATE House
SET CustomerType = CASE
    WHEN ... THEN...
    WHEN ... THEN...
    ELSE "Other"
    END
GO

This works in SQL Server Management Studio - it's not a T-SQL features, but a feature of Mgmt Studio to separate the T-SQL "batches" of SQL commands.

Update: if you want to make your script so that it can be run multiple times, the more useful approach would be to check for the column's existance, and only add it if it doesn't exist yet - no point in always dropping and re-adding it, if it's already there!

To do that, use something like:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = 'CustomerType' 
                       AND object_id = OBJECT_ID('House'))
   ALTER TABLE dbo.House ADD CustomerType CHAR(11)
GO

This snippet of code checks the SQL catalog view sys.columns to see if that column already exists in that table - if not, it's created. When you run this code against your table a thousand times, the first time around, the column gets created, and any subsequent run will do nothing - since that column already exists. Much cleaner than constantly dropping and re-adding a column!

Answer 3

Perhaps you have to commit your changes to the schema prior to executing the UPDATE.

The schema DDL should be settled by the time you get to DML/DQL. Why do you think you have to keep dropping and adding that column?

Answer 4

There has to be more to it than what you've shown above. I can't get a very similar script to fail; the following works just great:

CREATE TABLE #House (housekey int, number int, street char(20))

insert into #House (housekey, number, street) values (1, 123, 'Wilson Ave')
insert into #House (housekey, number, street) values (2, 124, 'Wilson Ave')
insert into #House (housekey, number, street) values (3, 125, 'Wilson Ave')

alter table #House DROP COLUMN street
alter table #House ADD street varchar(20)

update #House
set street = case
    when number = 123 then 'Wilson Ave'
    when number = 124 then 'Willson Ave'
    when number = 125 then 'Wulson Ave'
    else 'Xxy St'
end

select * from #house

drop table #house

I thought the idea of breaking up the script by putting "GO" after certain parts would be effective, but when I come to run the thing I can't get it to fail.

In other words, "It works on my machine." Is there anything you've left off that might make a difference?

EDIT TO ADD: Yes, the GO is the Answer. "It works on my machine" is a bit irresponsible here, I admit, because the question didn't deal with a temporary table -- but a regular one. I tried out the problem using a permanent table and got the same result as the questioner, and the GO fixed it. That seemed logical from the get-go, actually.