CODEWITHSUNDEEP
t-sqlsql-insert
Saiyanthou
Saiyanthou

Reputation: 142

Insert Into with Case on whole entry

I'm creating a script to run which will take all the stock in an old system and insert into the new system. However the old system has 1000's of erroneous part number data that I want to ignore on the import.

I've tried encapsulating the whole select in a case statement and iterations on that theme.

It's quite a large set of data so for simplicity sake I've taken the vital information into the below code.

INSERT INTO [LOT_TBL]
(
    Part_No   
    Total_Stock
)

--Ignore an insert if Part_No_Old NOT IN (SELECT Part_No from PART_TBL)

SELECT
    Part_No_Old
    Total_Stock
FROM OLD_ERP
WHERE Total_Stock > 0

The PART_TBL in the new system has a list of all the valid parts, so if any on the import don't match, ignore and move on to the next one.

Upvotes: 0

Views: 958

Answers (1)

GMB
GMB

Reputation: 222492

You can filter with exists:

insert into [lot_tbl] (part_no, total_stock)
select
    part_no_old,
    total_stock
from old_erp o
where 
    total_stock > 0
    and exists (
        select 1 from part_tbl p where p.part_no = o.part_no_old 
    )

Or, if part_nos are unique in part_tbl, you can use a join:

insert into [lot_tbl] (part_no, total_stock)
select
    o.part_no_old,
    o.total_stock
from old_erp o
inner join part_tbl p on p.part_no = o.part_no_old 
where o.total_stock > 0

Upvotes: 1

Related Questions