none
Need help with Tricky Query.

    Question


  • 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]

    Friday, October 18, 2013 4:01 PM

Answers

All replies

  • If the columns keep change and yet want reuse the same query go with Dynamic SQL. But still it may not help much in case of UNION, if same column  not  present in two tables and the data type.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, October 18, 2013 4:18 PM
  • Hi,

    PFB code, think that is what is expected

    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
    
    
    Declare @SQL1 Varchar(500)='Select ',@SQL2 Varchar(500)='Select '
    Declare @orgTab varchar(60) = 'Dept_CT',@BckTab varchar(100) = 'dbo_Dept_CT_2013_10_17T09_30_13'
    select @SQL1 = @SQL1 + '['+a.name+'] , ',@SQL2 = @SQL2 +case when b.name is null then 'NULL' else '['+ b.name+']' end + ' , '  from
    (select name from sys.columns where object_id = object_id(@orgTab)) a
    LEFT OUTER JOIN
    (select name from sys.columns where object_id = object_id(@BckTab) )b
    ON a.name = b.name
    
    set @SQL1 = SUBSTRING(@SQL1,1,Len(@SQL1)-1) + ' From '+@orgTab + ' UNION ALL ' + CHAR(13)
    set @SQL2 = SUBSTRING(@SQL2,1,Len(@SQL2)-1) + ' From '+@BckTab
    --PRINT (@SQL1 + @SQL2)
    EXEC (@SQL1 + @SQL2)

    Thanks

    Saravana Kumar C

    Friday, October 18, 2013 6:35 PM