Automated Alter Table

Database
N/A

Automated Alter Table

I’m trying to create a process which will take a Point-in-time / Snap Shot table and copy it into a Historical table.  Inserting the records is not a problem but because we do not ‘own’ the table there is a chance(and likely) new columns will be added to my source table.  I’ve tried to come up with a process to compare to Source and Target table and if they are different alter the target table.  Obviously the below does not work (that why I’m asking the form J ) but you can tell what I’m try to do.

Is this even possible and if so – how?  Than you for any feedback.

-Tye

ALTER TABLE xyz.TargetTable  ADD
SELECT ColumnName
 FROM DBC.Columns
 WHERE DATABASENAME = 'xyz'
 AND TABLENAME = 'SourceTable'
 AND ColumnName NOT IN
(SELECT ColumnName
FROM DBC.Columns
  WHERE DATABASENAME = 'xyz'
  AND TABLENAME = 'TargetTable')
Tags (2)