SQLTeam.com | Weblogs | Forums

Add/drop columns from a table in SQL without generating errors

How can I add/drop columns from a table in SQL without getting an error message when I rerun the code afterwards? When I've successfully dropped a column, it tells me the column is invalid and when I've successfully added one, it tells me the column already exists upon rerunning my program.

You can check if a table Exists before you run your code:
DROP TABLE IF EXISTS dbo.Test;
SELECT 1 AS test1, 2 AS test2 INTO dbo.Test;

This code will drop the column the first time and won't give an error the next time:
ALTER TABLE dbo.Test DROP COLUMN IF EXISTS test1;
ALTER TABLE dbo.Test DROP COLUMN IF EXISTS test1;

Use this code to add a new column over and over again bij first dropping it if it exists and add it:
ALTER TABLE dbo.Test DROP COLUMN IF EXISTS test1;
ALTER TABLE dbo.Test ADD test1 INT NULL;

It worked. Thank you for the helpful response! (I was using 'drop column if exists' before, but apparently I should have included a separate 'alter table' statement for each column.)