IF EXISTS (SELECT name FROM syscolumns
             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS')
BEGIN
   INSERT INTO TABLE1
   (
     COLUMN_THAT_NO_LONGER_EXISTS,
     COLUMN_B,
     COLUMN_C
   )
   SELECT 1,2,3 FROM TABLE2

   ALTER TABLE TABLE1 DROP COLUMN COLUMN_THAT_NO_LONGER_EXISTS
END

IF Not EXISTS (SELECT name FROM sys.columns
             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS' and Object_Name(object_id) = 'Table1')

IF Not EXISTS (SELECT name FROM sys.columns
             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS' and Object_Name(object_id) = 'Table1')

create table tblTests
(
TestID int identity (1,1),
TestColA int null,
TestColB int null
)

go -- Ran this on its own



insert into tblTests (TestColA, TestColB)
Select 1,2
go 10
-- Insert some initial data

alter table tblTests
add TestColC Int
go -- alter the table to add new column

-- Run this with column and then after it has removed it

IF EXISTS (SELECT name FROM sys.columns a
             WHERE name = 'TestColC' AND
             OBJECT_NAME(object_id) = 'tblTests')
Begin
    insert into tblTests (TestColA, TestColB, testcolc)
    select 1,2,3

    alter table tblTests
    drop column TestColC
End