Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2006
    Posts
    35

    Unanswered: Help with insert query

    Below is the query I'm using to insert data into a table based on data in two fields from another table.

    INSERT INTO RollCallsIND
    (RCID, district_id, member_name, result)
    SELECT PKRCID, 66 AS Expr1, 'davis' AS Expr2, 'N' AS Expr3
    FROM RollCalls
    WHERE (NoVoters LIKE N'%davis%')

    This works great, but I need to repeat the query twice (once of Yesvoters and once for NoVoters) for each of 81 members.

    The query works great, but the process is very time consuming!!

    Is there a way to automate this process? Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by smacky05 View Post
    Is there a way to automate this process?
    store the 81 names in a separate table and do a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    35

    Hmmm...

    I like it! Thanks.

  4. #4
    Join Date
    Dec 2006
    Posts
    35
    Okay - how do I configure the WHERE part of the query so that it matches the member_name to YesVoters and NoVoters fields? Here is what I have so far:

    INSERT INTO RollCallsIND
    (RCID, district_id, member_name, result,)
    SELECT RollCalls.PKRCID, Members.Dist, Members.AlphaShort, 'Y' AS Expr1, RollCalls.YesVoters
    FROM Members RIGHT OUTER JOIN
    RollCalls ON Members.AlphaShort = RollCalls.YesVoters

  5. #5
    Join Date
    Dec 2006
    Posts
    35

    No results using this

    Here is what I have. It produces 0 results.

    INSERT INTO RollCallsIND
    (district_id, member_name, RCID, result)
    SELECT Members.Dist, Members.AlphaShort, RollCalls.PKRCID, 'Y' AS Expr1
    FROM Members RIGHT OUTER JOIN
    RollCalls ON Members.AlphaShort LIKE RollCalls.YesVoters
    WHERE (Members.AlphaShort LIKE RollCalls.YesVoters)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    INSERT INTO RollCallsIND
         ( district_id
         , member_name
         , RCID
         , result)
    SELECT Members.Dist
         , Members.AlphaShort
         , RollCalls.PKRCID
         , 'Y' 
      FROM Members 
    INNER
      JOIN RollCalls 
        ON RollCalls.YesVoters LIKE '%'+Members.AlphaShort+'%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, on your select you should be able to do a union and get the yes's on one side and no's on the other.
    Dave

  8. #8
    Join Date
    Dec 2006
    Posts
    35
    Worked like a charm. You have saved me literally hundreds of hours. Thank you!

Tags for this Thread

Posting Permissions

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