Insert and update dimension table from sales tables based on condition

Question

Hi All, I’m working on project in which needs some enhancement in sql logic and for that need some help. There is a very big table dbo.sales that populates with each month of sales text files and I have made new dimension table dbo.supplier from dbo.sales by pulling distinct supplier Id, Supplier Name and Tin No. Now I want to populate table dbo.supplier incrementally in this way ..

1. If any new sales files come with new file id contains new supplier information then insert unique supplier Id, Supplier Name and Tin No. to dbo.supplier table.
2. If any new sales files come with new file id contains updated supplier information like old supplier got new Tin no or supplier changed his name then then query should update existing record with new information.
3. If there is no change in supplier data query should touch any thing in dbo.supplier table.

Answer 1

For this type of question it is always a good idea to post CREATE TABLE statements for your tables together with INSERT statements with sample data, sufficiently to illustrate all angles of the problem. Finally, also provide the expected results from the sample data. This makes it easy to copy and paste into a query window to develop a tested solution.

Thus, what follows is entirely untested: One remark: the <something> that appears is a placeholder for a column in the input that you need to use as a tie-breaker if there are multiple entries for a supplier in the input that might have conflicting information.

MERGE dbo.suppllier s
USING (SELECT SupplierID, SupplierName, TinNo,
              row_number() OVER(PARTITION BY SupplierID ORDER BY <something> DESC) AS rowno
       FROM   dbo.newfile) f ON f.SupplierID = s.SupplierID
                            AND f.rowno = 1
WHEN NOT MATCHED BY TARGET THEN
    INSERT (SupplierID, SupplierNmae, TinNo)
       VALUES(f.SupplierID, f.SupplierName, f.TinNo)
WHEN MATCHED AND NOT EXISTS (SELECT s.SupplierName, s.TinNo
                             INTERSECT
                             SELECT f.SupplierName, f.TinNo) THEN
   UPDATE SET SupplierName = f.SupplierName,
              TinNo        = f.TinNo
;
   

Answer 2

Hi @ASHISH SINGH ,

Welcome to the Q&A sql server transact sql forum!

It looks like you are posting a question on the QA forum for the first time.Unfortunately, for us, your question is not very clear.
As suggested by other experts, when you post a problem, please share with us the tables and insert data involved, and the output you expect (it can be images or excel data, etc.) so that the problem can be solved quickly.

The following is a solution based on my guess, please refer to, please refer to：

insert into supplier  
select supplierId, SupplierName,TinNo from dbo.newsalesfile  
where supplierId not in (select supplierId from dbo.sales)  
  
update supplier  
set supplier.SupplierName = n.SupplierName,  
    supplier.TinNo= n.TinNo  
from newsalesfile n  
where supplier.supplierId=n.supplierId   
and (supplier.SupplierName <> n.SupplierName or supplier.TinNo<> n.TinNo )  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table