Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2008
    Posts
    28

    Question Unanswered: Problem with INSERT command

    I'm having problems with my INSERT command. I suspect it may have something to do with the temp table but I can't find any documentation.

    Here is my current code:

    INSERT proc_resource
    SELECT
    proc_urn,
    surg_urn,
    res_urn,
    fac_urn,
    pr_quantity,
    pr_offset,
    pr_dur
    FROM
    #temp3

    I receive a the following error message:
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    This is strange since I'm not using a subquery. Oddly enough I swapped the tables so that I would add to the temp table instead and it works fine.

    INSERT #temp3
    SELECT
    proc_urn,
    surg_urn,
    res_urn,
    fac_urn,
    pr_quantity,
    pr_offset,
    pr_dur
    FROM
    proc_resource

    SELECT * FROM #temp3

    Any help would be great.

    Thanks,

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I dunno really but why don't you try putting in a destination column list.

    INSERT INTO MyTABLE(COLL1,COL2)
    SELECT COLL1,COL2 FROM myothertable
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2008
    Posts
    28
    I already tried that previously and I just ended up with the same error message.

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    what you pasted, is that a part of your code you think is bad? Can you paste the full code as you're running in the management studio?

  5. #5
    Join Date
    Sep 2005
    Posts
    161
    Do you have a trigger on proc_resource doing an insert? The error might be coming from a trigger.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...and that's another reason not to use triggers...oh boy...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Mar 2008
    Posts
    28
    Good catch Cascred! The table in question does in fact have a trigger. I would have spent forever trying to figure that one out. Since I can't modify the trigger (coded by the vendor) I wonder if a loop (cursor) sending one line at a time would work??

  8. #8
    Join Date
    Sep 2005
    Posts
    161
    First, I have to say that you have a piss-poor trigger when it assumes that only one item at a time will be inserted into a table.

    If you can't modify the trigger, inserting one line at a time is your only solution.

    I am not suggesting that you modify the vendor supplied trigger (that's my disclaimer). However, being a DBA, I would be tempted to modify the trigger if I could duplicate the functionality while fixing the flaw. Not that I have ever done anything like that before .....

  9. #9
    Join Date
    Mar 2008
    Posts
    28
    LOL...don't think I wasn't tempted.

    Unfortunately this vendor didn't think somebody like me would try to save our staff hours of data entry since their front end application can only handle one entry at a time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •