Post reply

Query Help - Mulitple inserts

  • August 29, 2005 at 3:01 pm

    #165368

    I have multiple insert statements that need to be executed.  Each insert statement is exactly the same except one value; is there any way I can do this more efficiently?  (I know what the value is going to be each time and it is not dependant on anything.)
     
    Example:
    INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID)
    SELECT  Org_ID, '86'
    FROM CQT_ORGANIZATION (nolock)
    WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock))
     
    INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID)
    SELECT  Org_ID, '88'
    FROM CQT_ORGANIZATION (nolock)
    WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock))
     
    INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID)
    SELECT  Org_ID, ‘92’
    FROM CQT_ORGANIZATION (nolock)
    WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock))
     
    There just has to be a way to combine these??…. Any suggestions you could give me I’d be greatly appreciated!!! 
     
    ps - I need a row inserted for each value so in the end it would look similiar to this:
    ORP_ORG_ID    ORP_PRD_ID
    1299               86
    1299               88
    1299               92  and so on......

  • August 29, 2005 at 3:11 pm

    #585993

    Glorianni...so your ORP_ORG_ID is not unique ?!?!

    How do you decide which number goes into the ORP_PRD_ID column - I know you said it is not dependent on anything...but I'm curious about how you decide which ORP_PRD_ID goes against which ORP_ORG_ID...also, do the #s have any sort of a pattern at all - ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 29, 2005 at 3:15 pm

    #585994

    Seems like a slightly odd request, but here goes:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    CREATE TABLE #prds

      (prd_id CHAR (2) NOT NULL)

    INSERT INTO #prds (prd_id) VALUES ('86')

    INSERT INTO #prds (prd_id) VALUES ('88')

    INSERT INTO #prds (prd_id) VALUES ('92')

    INSERT INTO CQT_ORGANIZATION_PRODUCT (ORP_ORG_ID, ORP_PRD_ID)

      SELECT c.Org_ID

           , p.prd_id

      FROM CQT_ORGANIZATION c

        CROSS JOIN #prds    p

      WHERE c.Org_ID not in (Select Orp_Org_ID

                             From CQT_ORGANIZATION_PRODUCT)

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    You have the option of eschewing the temp table and just making a SELECT ... UNION ALL SELECT ... list.  You also have the option of looping, but as per your specification, I don't see the need.

    The SET statement obviates the need for NOLOCK on each table.

  • August 29, 2005 at 5:25 pm

    #586019

    Thanks Lee!  I think your idea is great... is there any way to make it even more simplified?

    INSERT INTO #prds (prd_id) VALUES ('86')

    INSERT INTO #prds (prd_id) VALUES ('88')

    INSERT INTO #prds (prd_id) VALUES ('92')

    Can we shorten that part of the script?  I have about 256 seperate values...  

  • August 30, 2005 at 3:37 am

    #586076

    Where are the values coming from? If they are stored in a text document or spreadsheet you could use DTS to import the values into a table and then join the table you created.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • August 30, 2005 at 3:42 am

    #586077

    Well, if you have 256 separate values that have to be inserted always (is that so? For each Org_ID you insert 256 rows into the table with always the same values of prd_id?) then I would recommend that you create a permanent table with all these values instead of #prds. Then just modify the above SQL (by Lee Dise) to work with that table.

    Your original method - from first post - would IMHO not work the way you described, because once you insert the first row with '86', no other rows will be inserted for the same organization, because the where condition would not be met (there already is a row with this Org_ID in the table, i.e. the one you currently inserted). That is, you would not achieve the desired result described in the same post.

    Could you please describe a little more of the background - what does the table mean and why do you need to fill it this way? Maybe there is some other way to solve it....

    HTH, Vladan

  • August 30, 2005 at 7:45 am

    #586130

    If I'm working with one-time chunks of data that need to be inserted (or other commands such as update or delete), I often use an Excel spreadsheet to build the command strings for me. An Excel formula such as:

    ="insert into table1(column1, column2) values (" & A1 & "," & A2 & ")"

    will take the values in a1 and a2 and put them into the SQL string. Then you can propagate that formula all the way down the column for as many values as you have.

    When you're done, just copy that column with the formulas and paste into Query Analyzer and run the statements. The paste operation should paste the values that the formulas generate rather than the formulas themselves.

    Sometimes it's just easier to work in Excel because it's copy-and-paste operations are so flexible and cooperative with other programs.

    Cheers,

    d.

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

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