Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Insert statement not working

    I have this statement buried in a sproc:

    Code:
    INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID)
    SELECT PLAN_SHIP.[YEAR], PLAN_SHIP.BOD_INDEX, 1 
    FROM PLAN_SHIP LEFT JOIN PLAN_DEMAND ON 
    PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR] 
    AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX
    WHERE PLAN_DEMAND.BOD_INDEX IS NULL
    When I run the sproc in QA, the statements returns records to the grid, but does not insert them into the table. If I just copy the statement into QA and execute it, it works fine.

    I'm sure it's something obvious (but not to me).

    Any help would be much appreciated.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the code for your sproc. Either a logical data flow error is preventing the statement from being executed, or you have something commented out of your production code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    No can do

    I tried to post the sproc, but I'm limited to posting 10K characters.
    The sproc is about 15K.

    However...
    I commented out everything else in the sproc, execpt this one statement, and I still get the same thing.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Humor me. Create this procedure:
    Code:
    CREATE PROCEDURE MYTEST
    AS
    BEGIN
    
    INSERT	INTO PLAN_DEMAND
    	([YEAR],
    	BOD_INDEX,
    	SCEN_ID)
    SELECT	PLAN_SHIP.[YEAR],
    	PLAN_SHIP.BOD_INDEX,
    	1 
    FROM	PLAN_SHIP
    	LEFT JOIN PLAN_DEMAND
    		ON PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR] 
    		AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX
    WHERE	PLAN_DEMAND.BOD_INDEX IS NULL
    
    END
    Execute it, and let me know what you get.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Works perfectly... as- I'm sure- you expected.

    hmmm....
    Since the 2 statements are identical, the problem must lie elsewhere in the sproc. But then, why didn't it work when I commented out everything but this statement? Since the sproc is really just a series of insert/updates, I'll try posting one section at a time to a new sproc until I see the problem again.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you have GO statements in your sproc? Those are command terminators rather than SQL commands, and are not affected by commenting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I have 1 go statement, at the very end of the sproc.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    OK Blind dude.... Here's what I found:

    I tried to parse my original statement to look like yours, just for grins.
    After converting the first the first line from this:
    INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID)

    to this:
    INSERT INTO PLAN_DEMAND
    ([YEAR], BOD_INDEX, SCEN_ID)

    I got: ILLEGAL SYNTAX NEAR [YEAR]

    Everything looked ok, so I deleted the line: INSERT INTO PLAN_DEMAND
    and just retyped it. Works fine now. Must've been some illegal character
    between DEMAND and ([YEAR])

    Thanks for your help
    Inspiration Through Fermentation

Posting Permissions

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