How to insert a list of values

Question

I’m trying to create a stored procedure that is automatically able to populate a list of players in a team based on the team’s ID. What I’m trying to do is say that each team has 20 players, their first name matches the team name and their surname should begin with ‘Player’ and end with a number based on a count for the relevant team e.g.

    First Name                Surname   TeamID

    AFC Wimbledon             Player1   1

    AFC Wimbledon             Player2   1

    AFC Wimbledon             Player3   1

--All the way to 20 players     

    Angers                    Player1   2

I am not sure how to do this and need little guidance or a code sample so I can look to see how it’s done. I thought I may need an OUTPUT to help solve this but I think my logic is terribly flawed. Here are the tables: Team:

TeamID  TeamName
1        AFC Wimbledon
2        Angers
3        Ards

Player (currently empty as need this to be inserted to:

PlayerID    FirstName   Surame  TeamID

Below is my PROC currently:

CREATE PROCEDURE [dbo].[Player_Insert]

    @FirstName VARCHAR(25) OUTPUT,
    @Surname VARCHAR(25) OUTPUT,
    @TeamName VARCHAR(50)

AS
SET NOCOUNT ON
BEGIN

    DECLARE @TeamID INT, @CountPlayers INT

    SELECT @TeamID = TeamID FROM Team WHERE TeamName = @TeamName

    SELECT @CountPlayers = count(*) FROM Player WHERE TeamID = @TeamID

    IF @CountPlayers >= 20
    BEGIN
        RAISERROR('Reached the maximum limit of players for %s', 16, 1, @TeamName);
        RETURN;
    END

SET @FirstName = @TeamName
SET @Surname = 'Player' + @CountPlayers

    INSERT INTO Player (FirstName, Surname, TeamID)
    VALUES (@FirstName, @Surname, @TeamID)

END

Below is the EXEC PROC:

DECLARE  @FirstNameOutput VARCHAR(25)
DECLARE @SurnameOutput VARCHAR(25)


EXEC Player_Insert 
    @FirstName = @FirstNameOutput OUTPUT,
    @Surname = @SurnameOutput OUTPUT,
    @TeamId = 1

score 0 · Accepted Answer · edited May 23 '17 at 10:34

You will need NUMBERS table .Here are ways to create this

DEMO here :

---Table creattion script   
    create table #t
    (
    teamid int,
    teamname varchar(100)
    )

    insert into #t
    select '1','AFC Wimbledon'
    union all
    select '2','Angers'
    union all
    select '3 ','Ards'


--final query
    SELECT teamname as firstname,
     'player'+ cast(n  as varchar(5)) AS [surname],
     teamid
    FROM 
    #t
    CROSS APPLY 
    (select n from numbers nmbr where  nmbr.n<=20)  b
    order by teamid

score 0 · Answer 2 · answered Jun 01 '16 at 17:39

0

This should get you what you need.

;WITH TallyTable
AS (
    SELECT TOP 20 ROW_NUMBER() OVER (
            ORDER BY a.id
            ) AS N
    FROM Master.dbo.SysColumns a
        ,Master.dbo.SysColumns b
    )
INSERT INTO Player (FirstName, Surname, TeamID)
SELECT TeamName AS FirstName
    ,'Player' + CAST(N AS VARCHAR(2)) AS Surname
    ,TeamID
FROM Team
CROSS JOIN TallyTable
ORDER BY TeamID, N

answered Jun 01 '16 at 17:39

TLaV

389
2
4

To use this in a stored procedure, and filter by TeamID, you can just add WHERE TeamID = @TeamId below the CROSS JOIN statement. – TLaV Jun 01 '16 at 17:48

How to insert a list of values

2 Answers2