IF exists Question

  • I need to update an item table(item_tbl1 ) but only if the item number is in item_tbl2 and not already in item_tbl1

    I have 3000 inserts to do that look like this:

    INSERT INTO item_tbl1

    (SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)

    VALUES

    ('CORP1','0031','800281','0031','EA','DOM','800281000016')

    INSERT INTO item_tbl1

    (SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)

    VALUES

    ('CORP1','0032','800281','0032','EA','DOM','800281000029')

  • Where's the data coming from? Where are those 3000 source rows?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Pretty light on details but does this help?

    INSERT INTO item_tbl1

    (SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)

    select 'CORP1','0031','800281','0031','EA','DOM','800281000016'

    where not exists

    (select 'CORP1','0031','800281','0031','EA','DOM','800281000016' from item_tbl2)

    Of course this could be different depending on where the values are coming from. In other words, it would be preferred to make this a set based operation instead row by row inserts.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • the 3000 rows are in an excel spreadsheet that I was emailed to insert in to my two tables(3000 for each table 6000 total)

    I will copy these into studio and then run them was their plan but I already have some of these in the tables and I don't want to insert any that I do not have an item for in my master item table.

  • Here is my suggestion. Create an empty table, insert the data there, then using SQL, you can insert the appropriate records into the appropriate tables in a set-base manner.

  • I created another table that look like these

    SETIDINV_ITEM_IDMFG_IDMFG_ITM_IDPREFERRED_MFG

    CORP100318002810031Y

    CORP10031TM8002810031TMY

    CORP100328002810032Y

    CORP100518002810051Y

    CORP10051SP8002810051SPY

    CORP100528002810052Y

    CORP100618002810061Y

    CORP100628002810062Y

    CORP100718002810071Y

    CORP100728002810072Y

    It has 3000 rows and I want to update it's sister table but only where the inv_item_id is in the master_item_tbl and not in the sister table mfg_item

  • Write a select query first to help you identify the data you need to insert.

  • Maybe somethin' like this'll do you right:

    ;WITH InData

    (SETID,INV_ITEM_ID, MFG_ID, MFG_ITM_ID, UNIT_OF_MEASURE, UPN_TYPE_CD, UPN_ID)

    AS (

    SELECT 'CORP1','0031','800281','0031','EA','DOM','800281000016')

    UNION ALL SELECT 'CORP1','0032','800281','0032','EA','DOM','800281000029'

    )

    INSERT INTO item_tbl1

    SELECT * FROM InData i

    INNER JOIN item_tbl2 i2 ON i2.INV_ITEM_ID = i.INV_ITEM_ID

    LEFT OUTER JOIN item_tbl1 ON i1.INV_ITEM_ID = i.INV_ITEM_ID

    WHERE i1.INV_ITEM_ID IS NULL


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply