Migrate identified WDR data from new_table to old_table using View

Question

Input : Select * from new_table

Code	name
S001	WDR
S002	WDR
S005	AXC

Select * from Old_table

Code	name
S001	WDR
S003	WDR
S004	MNO

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

code	name	db_name
S001	WDR	New
S002	WDR	New
S003	WDR	old
S004	MNO	old

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.

@P.Salmon only MySQL code is there, but I am still getting an indentation error when I try to add my code. — Mohit, Nov 18 '22 at 11:02
@P.Salmon I have added the code which I am trying in the question itself. — Mohit, Nov 18 '22 at 11:15
I am puzzled why you thought a view would be useful here. You need an insert..select, followed by a delete (as separate statements) or possibly a delete on old with a trigger to push deleted into new. — P.Salmon, Nov 18 '22 at 11:26
@P.Salmon our requirements is so we have to amend the existing view to read the data — Mohit, Nov 18 '22 at 11:37
A view cannot migrate data it can only present it so you can never end up with 3 rows in table a and 1 in table b using this method - or the question is incorrectly presented.. — P.Salmon, Nov 18 '22 at 11:43
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249723/discussion-between-mohit-and-p-salmon). — Mohit, Nov 18 '22 at 12:05
I was a bit puzzled by the desired output, but now that you have corrected it, I suppose you want this: Per code you want one entry in the result table. Preferably from the new table, else from the old table. If a code does not match the Snnn pattern, you want a new S number for it. Correct? — Thorsten Kettner, Nov 21 '22 at 06:03
@ThorstenKettner yes you are correct, except for the matching pattern is WDR, — Mohit, Nov 21 '22 at 06:07
Ah, okay. Yes, you only want WDR from the new table. Your query is already very close to the solution it seems. Only that your query mistakenly prefers old over new and does not amend codes that don't match Snnn. Yes? — Thorsten Kettner, Nov 21 '22 at 06:14
@ThorstenKettner i have edited Input for better understanding, taking the record from new_table only if 'WDR' is present in the Name column and entry in the result table — Mohit, Nov 21 '22 at 06:15
Hm, okay. Now there is no code amending anymore. You simply take all WDR rows from new_table plus all rows from old_table, the code of which does not exist in new_table. If you correct the syntax errors in your query, then you only need to change `order by db_name DESC` to `order by db_name` to get this result. But you can achieve the same with a much simpler query. Please see my answer on this. — Thorsten Kettner, Nov 21 '22 at 06:57

Thorsten Kettner · Accepted Answer · 2022-11-21T07:22:53.820

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_

i want all record old table+WDR from new _table. In this answer, Code S004 row is missing in the result set. — Mohit, Nov 21 '22 at 07:13
No, it is not missing. Please see the Demo I have linked to in my answer. — Thorsten Kettner, Nov 21 '22 at 07:23

Migrate identified WDR data from new_table to old_table using View

1 Answers1