Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: Insert/Updated SP from multiple tables

    How do I insert unrelated statistical data from three tables into another
    table that already exist with data using an insert or update stored procedure?
    OR...
    How do I write an insert/Update stored procedure that has multiple select
    and a where something = something statements?

    This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this....

    CREATE PROCEDURE AddDrawStats
    AS
    INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)

    SELECT
    WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),

    TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

    Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),

    RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )

    FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID

    WHERE D.DrawingID = DS.CurrentDrawing
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing"
    and
    "WHERE W.DrawingID = DS.CurrentDrawing"
    are redundant. They both accomplish the same thing; associating records in the two tables. Among SQL Server DBAs, the INNER JOIN syntax is preferred, so drop the links in your WHERE clauses.

    As to your other issues, I'm sorry but the SQL statement you posted is too disjointed to figure out what your intentions are. You will need to describe your tables and your objective if you want more help, but embedding subqueries into the SELECT clause is rarely a good idea. I highly suspect that what your SQL statement describes is not really what you are trying to do.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2004
    Posts
    7
    yes my attention is that I have Four related/non-related table and I would like to get some statistical data such as the count of how many student are in the student table, how many student are playing the current drawing, how many tickets are in the current drawing, and how many students won the current drawing. Setting up a common inner join would not allow me to get the exact data I need. Plus, I need to insert this data in the drawing table record that already have data but these fields are null. My stored procedure works somewhat, but it creates a new record; I want the stored procedure to insert this information in the record that already exist where drawing = the CurrentDrawing. So should I do an insert/update stored procedure, and how? All I need to see is an example of a stored procedure that insert or update data in some table where some criteria are met which comes from multiple select statements using different table within those select statement.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is what your SQL Statement describes, but again, I doubt that it is exactly what you want:

    CREATE PROCEDURE AddDrawStats
    AS

    Declare @Players int
    Declare @RegisterPlayers int
    Declare @TicketsPlayed int

    set @Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
    set @RegisterPlayers = (SELECT Count(*) FROM Student)
    set @TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),

    Update Drawing
    set WinnersWon = WinnersSubquery.WinnersWon,
    TicketsPlayed = @TicketsPlayed,
    Players = @Players,
    RegisterPlayers = @RegisterPlayers
    from Drawing
    inner join
    (SELECT DS.CurrentDrawing, count(*) as WinnersWon
    FROM DrawSetting DS
    INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
    GROUP BY DS.CurrentDrawing) WinnersSubquery
    on Drawing.DrawingID = WinnersSubquery.CurrentDrawing

    INSERT INTO Drawing (DrawingID, WinnersWon, TicketsPlayed, Players, RegisterPlayers)
    select WinnersSubquery.CurrentDrawing,
    WinnersSubquery.WinnersWon,
    @TicketsPlayed,
    @Players,
    @RegisterPlayers
    from (SELECT DS.CurrentDrawing, count(*) as WinnersWon
    FROM DrawSetting DS
    INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
    GROUP BY DS.CurrentDrawing) WinnersSubquery
    left outer join Drawing on WinnersSubquery.CurrentDrawing = Drawing.DrawingID
    where Drawing.DrawingID is null
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Nov 2004
    Posts
    7
    Thanks for all the help! This works but I have two questions?
    Could I have written this Stored procedure better? and...
    Why this statement yeilds the wrong results? *i.e.*each player can have up to five tickets in the ticket table, but this statement count each ticket as a player.How do I write this statement to get only unigue AccountID within the ticket table?
    **SET @Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)


    CREATE PROCEDURE AddDrawStats2
    AS

    DECLARE @WinnersWon INT
    DECLARE @TicketsPlayed INT
    DECLARE @Players INT
    DECLARE @RegisterPlayers INT

    SET @WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
    SET @TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
    SET @Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
    SET @RegisterPlayers = (SELECT COUNT(*) FROM Student )


    UPDATE Drawing
    SET WinnersWon = @WinnersWon,
    TicketsPlayed = @TicketsPlayed,
    Players = @Players,
    RegisterPlayers = @RegisterPlayers

    WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
    GO

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SET @Players = (SELECT Count(Distinct T.AccountID) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2004
    Posts
    7
    Thanks so much for all the help. This stored procedure does the job, but you see any drawbacks?

    CREATE PROCEDURE AddDrawStats
    AS
    DECLARE @WinnersWon INT
    DECLARE @TicketsPlayed INT
    DECLARE @Players INT
    DECLARE @RegisterPlayers INT

    SET @WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
    SET @TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
    SET @Players =(SELECT Count(Distinct T.AccountID) FROM Ticket T WHERE T.Active = 1)
    SET @RegisterPlayers = (SELECT COUNT(*) FROM Student )

    UPDATE Drawing
    SET
    WinnersWon = @WinnersWon,
    TicketsPlayed = @TicketsPlayed,
    Players = @Players,
    RegisterPlayers = @RegisterPlayers
    WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
    GO

Posting Permissions

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