Merging two large tables physically in SQL Server 2014

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *