I have a requirement to merge two large tables with redundant data.
Table A and Table B both have more than a billion records, most of the data including columns are redundant except 5 additional columns in Table B.
I would like to merge them physically and delete table B by comparing the data between both table and load the data that doesn’t exist in Table A but exist in Table B to Table A.
Ultimate goal is to get rid of Table B and load all data and columns to Table A and then partition table A.
Did anyone had experience doing this? How can I compare the data between two large tables? Can we use Informatica or any native SQL tools? Is it a good approach to merge the tables and partition Table A or first partition Table A and them merge the tables?
Please let me know if you need any details.
going to take a while on 3 billion records but this is a query
select tableB.* from tableB left join tableA on tableA.ID = tableB.ID where tableA.ID is null