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.
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')