Post reply

Insert statement

  • April 5, 2006 at 7:05 pm

    #167678

    I can't figure out this syntax. I get an error with this statement that subquery returns more than 1 value which is not allowed.

    BEGIN TRANSACTION

    INSERT INTO Person(

     PartyID, 

     LastName

    )

    SELECT

     (SELECT

     p.PartyID,

     d.LastName

    FROM Client d INNER JOIN Party p ON d.ClientID=p.PKID)

    GO

    COMMIT

    I'm trying to move a bunch of records from a client table into the Party table.

    Thanks

    Sam

  • April 5, 2006 at 7:33 pm

    #631130

    sam - remove the first select...

    INSERT INTO Person(PartyID, LastName)
SELECT p.PartyID,
       d.LastName
FROM   Client d 
INNER JOIN Party p 
ON 
d.ClientID=p.PKID

    though the error msg you get is curious..what do you get when you run only:

    SELECT
 p.PartyID,
 d.LastName
FROM Client d INNER JOIN Party p ON d.ClientID=p.PKID







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

  • April 5, 2006 at 7:50 pm

    #631133

    thanks. That was it. I had an extra "Select" 🙁

    It's working now.

    Sam

  • April 6, 2006 at 5:37 am

    #631175

    sushila,

    the message looks OK to me... the extra SELECT has no FROM, so it only allows for a single value/row (like SELECT 1), not entire rowset as selected from the query. Although when I tried it with a similar SQL, I was getting another message:

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    The message mentioned in original post appeared only after I limited the output to 1 column.

  • April 6, 2006 at 6:19 am

    #631189

    - said it was curious because running the SQL asis resulted in "The select list for the INSERT statement contains fewer items than the insert list...." not the msg that was posted....as you said, it appeared only after you limited the output to 1 column!







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

Viewing 5 posts - 1 through 5 (of 5 total)

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