quote:

---

Originally posted by NguyenL71


How can I do these:
1. Every time we add or drop a column, the process automatilly copy the original table and timestamp it with date/time
2. I need to write the queries UNION from the new table and the old table to get the data for reporting purpose. The
trick is I have about 400 tables. Please see desired results below. SQL 2012.

Sometime the column get drop, create or event modify the datatype is get copy with a timestamp.

Thanks so much in advance.


CREATE TABLE [dbo].[Dept_CT]
(
[__$start_lsn] [binary](10) NOT NULL,
[__$seqval] [binary](10) NOT NULL,
[__$operation] [int] NOT NULL,
[__$update_mask] [varbinary](128) NOT NULL,
[depid] [int] NULL,
[Decr] [varchar](25) NULL,
[typecd] [varchar](5) NULL,
[typedesc] [varchar](60) NULL,
[email] VARCHAR(100) NULL
[Effective_date] [datetime] NOT NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo_Dept_CT_2013_10_17T09_30_13]
(
[__$start_lsn] [binary](10) NOT NULL,
[__$seqval] [binary](10) NOT NULL,
[__$operation] [int] NOT NULL,
[__$update_mask] [varbinary](128) NOT NULL,
[depid] [int] NULL,
[Decr] [varchar](25) NULL,
[typecd] [varchar](5) NULL,
[typedesc] [varchar](60) NULL,
[Effective_date] [datetime] NOT NULL
) ON [PRIMARY]

GO


What I try to accomplish is to write a report from an old data which is in [dbo_Dept_CT_2013_10_17T09_30_13]
merge with Dept_CT which has new column.


-- Result want:

SELECT $operation]
,[depid]
,[Decr]
,[typecd]
,[typedesc]
,[email] --- This is a new column
,[Effective_date]
FROM dbo.Dept_CT
UNION
SELECT [depid]
,[Decr]
,[typecd]
,[typedesc]
,NULL -- no column
,[Effective_date]
FROM [dbo_Dept_CT_2013_10_17T09_30_13]

---