?
Solved

Need help with SQL INSERT query

Posted on 2009-12-20
4
Medium Priority
?
184 Views
Last Modified: 2012-05-08
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
Comment
Question by:DanLockwood
  • 2
4 Comments
 
LVL 2

Expert Comment

by:vinodsnair2001
ID: 26093859
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
 
LVL 30

Accepted Solution

by:
Reza Rad earned 2000 total points
ID: 26093865
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
 
LVL 11

Expert Comment

by:anilallewar
ID: 26093866
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
 
LVL 11

Expert Comment

by:anilallewar
ID: 26093884
Agree with @reza_rad that NOT IN could be used; but EXIST would be faster performance wise.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question