• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

Need help with SQL INSERT query

I'm trying to build an insert query that will insert rows into tblProduct from tblSource only when the rows in tblSource are not already present in tblProduct.  Here's what I have so far:

INSERT INTO tblProduct
                      (ProductNumber, IMProductNumber, ProductDescription, MSRP, IMPrice, Weight, Length, Width, Height, Taxable, fkManufacturer)
SELECT     TOP (5) tblIngramMicroImport.VendorPartNumber, tblIngramMicroImport.IMPartNumber,
                      tblIngramMicroImport.Description1 + ' ' + tblIngramMicroImport.Description2 AS Description, tblIngramMicroImport.RetailPrice,
                      tblIngramMicroImport.CustomerPrice, tblIngramMicroImport.Weight, tblIngramMicroImport.Length, tblIngramMicroImport.Width,
                      tblIngramMicroImport.Height, 1 AS Taxable, 1 AS fkManufacturer
FROM         tblIngramMicroImport INNER JOIN
                      tblProduct AS tblProduct_1 ON tblIngramMicroImport.VendorPartNumber <> tblProduct_1.ProductNumber

This works fine except it always inserts the rows into tblProduct regardless of them already being there.  I'm using the field 'VendorPartNumber' in the source table and 'ProductNumber' in the destination table to determine if the row already exists.  Can someone clue me in here?
0
DanLockwood
Asked:
DanLockwood
  • 2
1 Solution
 
vinodsnair2001Commented:
You can make sure that you do not insert duplicate information by using the EXISTS condition.

Eg query:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

Pls mark yes if this was helpful
0
 
Reza RadConsultant, TrainerCommented:
use not in,

try this query:

INSERT INTO tblProduct
                      (ProductNumber, IMProductNumber, ProductDescription, MSRP, IMPrice, Weight, Length, Width, Height, Taxable, fkManufacturer)
SELECT     TOP (5) tblIngramMicroImport.VendorPartNumber, tblIngramMicroImport.IMPartNumber,
                      tblIngramMicroImport.Description1 + ' ' + tblIngramMicroImport.Description2 AS Description, tblIngramMicroImport.RetailPrice,
                      tblIngramMicroImport.CustomerPrice, tblIngramMicroImport.Weight, tblIngramMicroImport.Length, tblIngramMicroImport.Width,
                      tblIngramMicroImport.Height, 1 AS Taxable, 1 AS fkManufacturer
FROM         tblIngramMicroImport 
where tblIngramMicroImport.VendorPartNumber not in
(select tblProduct_1.ProductNumber from tblProduct tblProduct_1)

Open in new window

0
 
anilallewarCommented:
Can you try with this? You would not get only the relevant rows because in your join you are comparing source.PK <> target.PK. So if source has 1,2 and target has 1,2,3; the query would find that 1<>2 and insert row for 2.
INSERT INTO tblProduct
                      (ProductNumber, IMProductNumber, ProductDescription, MSRP, IMPrice, Weight, Length, Width, Height, Taxable, fkManufacturer)
SELECT     TOP (5) tblIngramMicroImport.VendorPartNumber, tblIngramMicroImport.IMPartNumber,
                      tblIngramMicroImport.Description1 + ' ' + tblIngramMicroImport.Description2 AS Description, tblIngramMicroImport.RetailPrice,
                      tblIngramMicroImport.CustomerPrice, tblIngramMicroImport.Weight, tblIngramMicroImport.Length, tblIngramMicroImport.Width,
                      tblIngramMicroImport.Height, 1 AS Taxable, 1 AS fkManufacturer
FROM         tblIngramMicroImport source
where NOT EXISTS (Select 1 from tblProduct target where source.VendorPartNumber = target.ProductNumber)

Open in new window

0
 
anilallewarCommented:
Agree with @reza_rad that NOT IN could be used; but EXIST would be faster performance wise.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now