2

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?

2

IF Syntax is for more than one sql line:

IF (whatever)
   BEGIN
       sql line 1
       sql line 2
       .....
       sql line n
   END

good luck

3

Looking at the code, I don't see a reason for the cursor. It's unclear what column is supposed to be unique, but you can handle your EXIST in a WHERE clause (i used NOT IN) and remove all the variables, and cursor as well.

insert into purchase_order (order_number, job_id, supplier_id, contact_id, user_list_id, status_id, notes)
SELECT 
    a.order_id
    ,j.job_id
    ,s.supplier_id
    ,sc.contact_id
    ,ul.user_list_id
    ,2
    ,null
FROM 
    _po_temp_b b
inner join
    _po_temp_a a on a.order_id = b.order_id
inner join
    job j on j.job_number = b.job_num
inner join
    supplier s on s.supplier_name = b.supp_name
inner join 
    supplier_contact sc on sc.supplier_id = s.supplier_id
inner join
    user_list ul on ul.user_order_number = b.user_num
where
    a.order_number not in (SELECT order_number FROM purchase_order)

You may need LEFT joins... it all depends.

  • Setting this up, I get the same issue of still getting repeat entries. So for some reason, the WHERE clause seems to not be doing anything. – Skitzafreak Jul 17 '18 at 13:03
  • 1
    The where clause is definitely limiting the results (assuming there are matching records), but that doesn't mean you don't have duplicate order_id in the _po_temp_b table. If that's the case, you can use DISTINCT – scsimon Jul 17 '18 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.