Insert non-duplicate data into a table from another table

Question

I am trying to insert a non duplicate records into a table but which doing so I am unable to insert it it gives me (0 row(s) affected). And actually the table itself is empty and I am insert new records. To ignore inserting duplicate records I am doing it in the query which I have added below.

Insert Into OrderDetails (CustomerID, CustomerName, OrderID, OrderDate, ProductID, ProductName, Unit, Price)
SELECT c.CustomerID, c.CustomerName, i.OrderID, i.OrderDate, p.ProductID, p.ProductName, p.Unit, p.Price
FROM Customers c
INNER JOIN items i ON c.CustomerID=i.CustomerID
INNER JOIN itemsDetails id ON i.OrderID = id.OrderID
INNER JOIN Products P ON p.ProductID = id.ProductID
Inner Join OrderDetails od ON c.CustomerID = od.CustomerID
where c.CustomerID != od.CustomerID

Am I Inserting correctly?

how many rows does your `select` statement alone return? You are `inserting` into `orderdetails` and you say the table is empty. The table is also in the `join` clause. Hence you will not get any rows from `select`. I suggest you remove `orderdetails` from `join`. — Vamsi Prabhala, Aug 25 '16 at 14:07
try the select statement in your DB on it's own and check if any values are returned — LuckAss, Aug 25 '16 at 14:08
@LuckAss it shows 0 records as My Orderdetails is empty and I am inserting fresh records to that table. — , Aug 25 '16 at 14:10
that's the problem, if you join without any records, there are no records ;) — LuckAss, Aug 25 '16 at 14:12

score 0 · Accepted Answer · answered Aug 25 '16 at 14:14

try:

Insert Into OrderDetails (CustomerID, CustomerName, OrderID, OrderDate, ProductID, ProductName, Unit, Price)
SELECT c.CustomerID, c.CustomerName, i.OrderID, i.OrderDate, p.ProductID, p.ProductName, p.Unit, p.Price
FROM Customers c
INNER JOIN items i ON c.CustomerID=i.CustomerID
INNER JOIN itemsDetails id ON i.OrderID = id.OrderID
INNER JOIN Products P ON p.ProductID = id.ProductID
where c.CustomerID NOT IN (SELECT customerid FROM orderdetails)

score 0 · Answer 2 · answered Aug 25 '16 at 14:14

The error lies in the two last rows of your statement:

Inner Join OrderDetails od ON c.CustomerID = od.CustomerID
where c.CustomerID != od.CustomerID

In the inner join, you require the CustomerID be the same. In the where clause, you require the CustomerID to be different.

Remove the inner join of the table OrderDetails.

Insert non-duplicate data into a table from another table

2 Answers2