grMd6Ti5

1IF EXISTS (SELECT name FROM syscolumns 
2             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS')
3BEGIN
4   INSERT INTO TABLE1
5   (
6     COLUMN_THAT_NO_LONGER_EXISTS,
7     COLUMN_B,
8     COLUMN_C
9   )
10   SELECT 1,2,3 FROM TABLE2
11
12   ALTER TABLE TABLE1 DROP COLUMN COLUMN_THAT_NO_LONGER_EXISTS
13END
14	
15IF Not EXISTS (SELECT name FROM sys.columns 
16             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS' and Object_Name(object_id) = 'Table1')
17	
18IF Not EXISTS (SELECT name FROM sys.columns 
19             WHERE name = 'COLUMN_THAT_NO_LONGER_EXISTS' and Object_Name(object_id) = 'Table1')
20	
21create table tblTests
22(
23TestID int identity (1,1),
24TestColA int null,
25TestColB int null
26)
27
28go -- Ran this on its own
29
30
31
32insert into tblTests (TestColA, TestColB)
33Select 1,2
34go 10
35-- Insert some initial data
36
37alter table tblTests
38add TestColC Int
39go -- alter the table to add new column
40
41-- Run this with column and then after it has removed it
42
43IF EXISTS (SELECT name FROM sys.columns a 
44             WHERE name = 'TestColC' AND
45             OBJECT_NAME(object_id) = 'tblTests')
46Begin
47    insert into tblTests (TestColA, TestColB, testcolc)
48    select 1,2,3
49
50    alter table tblTests
51    drop column TestColC
52End