Insert or Update Data into Table with Foreign Key from Another Table

Question

Have two tables

Table VendorMaster
|VendorID|Vendor|
|    1   |Vendor1|
|    2   |Vendor2|

Table Data
|DataID|VendorID|ProductName|Quantity|
|   1  |    1   |  Product1 |   100  |
|   2  |    1   |  Product2 |   200  |
|   3  |    2   |  Product1 |   400  |
|   4  |    2   |  Product2 |   100  |

A vendor can have multiple products which are referred using foreign key Now I have another table used for staging the data

Table Staging
|StagingID|Vendor|ProductName|Quantity|

I need to insert or update the data into the Data table and Vendor table from the Staging table. Like the staging table can be used to update only the quantities or add new vendor and / or product.

How do I proceed with it? I am using SQL Server.

I tried using this, but it cannot update / insert the foreign key master table.

need more information to see what problem you are facing and how you map `staging` table with `data`and `vendor`table — Shankar, Feb 28 '19 at 07:32

score 3 · Accepted Answer · answered Feb 28 '19 at 07:38

You need to write below code to insert & update respective tables and I am assuming that VendorID & DataID columns are identity columns.

insert into VendorMaster (Vendor)
select Vendor
from Staging s
where not exists (
select 1 from  VendorMaster v 
where v.vendor = s.vendor
);


Update d
set Quantitty = s.Quantity
from Data d
inner join venderMaster v on d.vendorid = v.vendorid
inner join staging s on s.vendor = v.vendor 
and s.productName = d.productname
and d.Quantity <> s.Quantity;

insert into Data (VendorID,ProductName,Quantity)
select VendorID,ProductName,Quatity
from Vendor v
inner join staging s on s.Vendor = v.Vendor 
where not exists
(
 select 1 from Data d 
where d.vendorid = v.vendorid
and s.productName = d.productname
);

score 1 · Answer 2 · answered Feb 28 '19 at 07:38

You can do this in 2 steps in this particular order:

Keep the VendorMaster table up to date with new vendors. I'm assuming that the vendor name is your key here (it shouldn't be the name though, what happens if you have 2 different vendors with same name?) and VendorID is an IDENTITY.
```
INSERT INTO VendorMaster (
    Vendor)
SELECT DISTINCT
    S.Vendor
FROM
    Staging AS S
WHERE
    NOT EXISTS (SELECT 'vendor not yet loaded' FROM VendorMaster AS V
                WHERE V.Vendor = S.Vendor)
```

Update (if exists) & Insert (if not exists) the data from each vendor. I'm assuming the key on this table will be VendorID and ProductName (it shouldn't be product name, it should be ProductID, or the such), and that DataID is IDENTITY. The operations must be in this particular order to prevent pointless updating. The update won't update if it doesn't exist and the insert won't insert if it already exist:

UPDATE D SET
    Quantity = S.Quantity
FROM
    Staging AS S
    INNER JOIN VendorMaster AS V ON S.Vendor = V.Vendor -- Fetch the VendorID from VendorMaster
    INNER JOIN Data AS D ON
        V.VendorID = D.VendorID AND
        S.ProductName = D.ProductName
WHERE
    S.Quantity <> D.Quantity -- Update only if there are differences

INSERT INTO Data (
    VendorID,
    ProductName,
    Quantity)
SELECT
    VendorID = V.VendorID,
    ProductName = S.ProductName,
    Quantity = S.Quantity
FROM
    Staging AS S
    INNER JOIN VendorMaster AS V ON S.Vendor = V.Vendor -- Fetch the VendorID from VendorMaster
WHERE
    NOT EXISTS (
        SELECT
            'data not yet loaded'
        FROM
            Data AS D
        WHERE
            V.VendorID = D.VendorID AND
            S.ProductName = D.ProductName)

Insert or Update Data into Table with Foreign Key from Another Table

2 Answers2