Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Insert statement with multiple select statements

    hi

    first of all is it possible? if so, what am i doing wrong with this


    Code:
     INSERT into TB2
    
    (
    
    ClientCode,
    EngagementCode,
    EngagementDescription
    
    )
    
    
    
    
    	SELECT
    		(SELECT     dbo.tarCustomer.CustID
                 		FROM         dbo.tPA00175 INNER JOIN
                        	 	 dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
                        	 	 dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
    
    	NULL,
    
    	SELECT
    		(SELECT     dbo.tPA00175.chrJobNumber
    		FROM      dbo.tPA00175 INNER JOIN
                        		  dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
                        	  	dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
    the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.

    this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks

    tibor

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT 
      into TB2
         ( ClientCode
         , EngagementCode
         , EngagementDescription )
    SELECT dbo.tarCustomer.CustID
         , NULL
         , dbo.tPA00175.chrJobNumber
      FROM dbo.tPA00175 
    INNER 
      JOIN dbo.tarCustomer 
        ON dbo.tarCustomer.CustKey = dbo.tPA00175.CustKey
    INNER 
      JOIN dbo.tPA00007 
        ON dbo.tPA00007.intJobKey = dbo.tPA00175.intJobKey
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    82
    Thanks alot.. Something so simple but yet such a pain.

    have a good one

  4. #4
    Join Date
    Mar 2006
    Posts
    82
    Ok the syntax works great but its not doing the insert. i built the select statement with a view so i know all the links are nice and strong and such. it doesnt seem to like the 'from' i think. any suggestions?
    Last edited by tibor; 08-29-06 at 09:10.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you getting any error messages? I don't see anything wrong with r937's code (other than the goofy formatting ).
    If you run just the SELECT portion without the insert, does it return records? Are there any constraints on the target table that would prevent inserts (unique indexes, foreign keys....)?
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Mar 2006
    Posts
    82
    i apologize for not paying attention. the second part of my select statement had a join that returned nothing so i had to alter that. now its working but im getting the
    String or binary data would be truncated
    error. After i figure out which field is causing that, it should be all good. but stay tuned in case something else comes up, haha.

    tibor

Posting Permissions

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