Input : Select * from new_table

Select * from Old_table

cache here is we can't modify the table new_table/old_table data like

https://stackoverflow.com/questions/13237623/copy-data-into-another-table.

We have to migrate WDR data to new table.

Output of the result I am expecting

select * from Dummy

As this is migration so the identified data WDR. In this case, we have to design/amend our below code in such a manner that it should ingest the existing data from the current Old_table Migrated data from new_table

code which I am trying 

    create View Dummy
    As
    with input As
    (
    Select Distinct Code     =ne.Code,
                    name     =ne.name
                    db_name  =CAST('New' as char(3))
    from new_table As ne 
    where name like '%WDR%'
    
    Union All 
    
    select Distinct code      =ol.Code,
                    name      =ol.name
                    db_name  =CAST('old' as char(3))
    from old_table As ol
    ),
    data As 
    (
    select code=input.code,
           name=input.name,
           ranking=Row_Number() over(partition by code order by db_name DESC)
    from input
    )
    Select code            =data.code,
           name            =data.name
           db_name         =data.db_name  
    from data
    where data.ranking=1;

please amended the code.
 

CodePudding user response：

There are just some missing commas in your query, the column db_name missing in your data cte, and ORDER BY db_name DESC where it must be ORDER BY db_name ASC.

Apart from that, DISTINCT is probably unnecessary in your query, and it is not necessary to give a column an alias that matches the column name.

What remains is

create view dummy as
with input as
(
  select
    code,
    name,
    'new' as db_name
  from new_table
  where name like '%WDR%'

  union all 

  select
    code,
    name,
    'old' as db_name
  from old_table
),
data As 
(
  select
    code,
    name,
    db_name,
    row_number() over(partition by code order by db_name) as ranking
  from input
)
select 
  code,
  name,
  db_name  
from data
where data.ranking = 1;

Which can also be written as

create view dummy as
select code, name, 'new' as db_name
from new_table
where name like '%WDR%'
union all
select code, name, 'old' as db_name
from old_table
where code not in (select code from new_table);

Demo: https://dbfiddle.uk/7C4g3V9_