I am trying to insert a large amount of data into a table. The source for the data has the potential to repeat data in what is suppose to be a
UNIQUE column. I created the following to insert the data into my table:
DECLARE @OrderNum VARCHAR(20), @JobNum VARCHAR(20), @Supplier VARCHAR(100), @OldOrderID int, @UserNum int, @OrderID int, @JobID int, @SupplierID int, @ContactID int, @UserID int DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT order_id, job_num, supp_name, user_num FROM _po_temp_b OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @OldOrderID, @JobNum, @Supplier, @UserNum WHILE @@FETCH_STATUS = 0 BEGIN SELECT @OrderNum = order_num FROM _po_temp_a WHERE order_id = @OldOrderID IF NOT EXISTS(SELECT order_number FROM purchase_order WHERE order_number = @OrderNum) SELECT @JobID = j.job_id FROM job AS j WHERE j.job_number = @JobNum SELECT @SupplierID = s.supplier_id FROM supplier AS s WHERE s.supplier_name = @Supplier SELECT @ContactID = sc.contact_id FROM supplier_contact AS sc WHERE sc.supplier_id = @SupplierID SELECT @UserID = ul.user_list_id FROM user_list AS ul WHERE ul.user_order_number = @UserNum INSERT purchase_order(order_number, job_id, supplier_id, contact_id, user_list_id, status_id, notes) VALUES (@OrderNum, @JobID, @SupplierID, @ContactID, @UserID, 2, NULL); FETCH NEXT FROM MY_CURSOR INTO @OldOrderID, @JobNum, @Supplier, @UserNum END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR GO
Now my thought was that the
IF NOT EXISTS line would stop me from getting a bunch of data repetitions. However, I am still getting repeat data entries, preventing me from setting the column up as
UNIQUE, which is what it needs to be.
What exactly is wrong with my setup? Am I missing something somewhere?