Add rows to a table then loop back and add more rows for a different userid

Question

I have a table with 4 columns - ID, ClubID, FitnessTestNameID and DisplayName I have another table called Club and it has ID and Name

I want to add two rows of data to the 1st table for each club

I can write a statement like this, but can someone tell me how to create a loop so that I can insert the two rows, set the @clubid + 1 and then loop back again?

declare @clubid int
set @clubid = 1

insert FitnessTestsByClub (ClubID,FitnessTestNameID,DisplayName)
values (@clubid,'1','Height (cm)')

insert FitnessTestsByClub (ClubID,FitnessTestNameID,DisplayName)
values (@clubid,'2','Weight (kg)')

Why did you change the tags? Add the platform that you're using — Kermit
– Kermit, Commented Apr 29, 2013 at 15:58
Post the tables' CREATE statements. Are some columns IDENTITY? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 29, 2013 at 16:54

ypercubeᵀᴹ · Accepted Answer · 2013-04-29 17:17:08Z

3

You can probably do this with one statement only. No need for loops:

INSERT INTO FitnessTestsByClub 
    (ClubID, FitnessTestNameID, DisplayName)
SELECT
    c.ID, v.FitnessTestNameID, v.DisplayName
FROM
    Club AS c
  CROSS JOIN
    ( VALUES
        (1, 'Height (cm)'),
        (2, 'Weight (kg)') 
    ) AS v (FitnessTestNameID, DisplayName)
WHERE 
    NOT EXISTS                             -- a condition so no duplicates
    ( SELECT *                             -- are inserted
      FROM FitnessTestsByClub AS f         -- and the statement can be run again
      WHERE f.ClubID = c.ID                -- in the future, when more clubs
    )                                      -- have been added.
  ;

The Table Value Constructor syntax above (the (VALUES ...) construction) is valid from version 2008 and later.

There is a nice article with lots of useful examples of how to use them, by Robert Sheldon: Table Value Constructors in SQL Server 2008

edited Apr 29, 2013 at 17:17

answered Apr 29, 2013 at 16:59

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

markA Over a year ago

You are assuming he doesn't want to add this for future clubs not yet in the club table and that he will run this when a new clubs is added with some sort of "if these don't already exist" logic

ypercubeᵀᴹ Over a year ago

@jimdrang No, I'm not. When clubs are added in the future, in the Club table, the statement (slightly changed) can insert again 2 rows per club.

markA Over a year ago

You either assume he doesn't want to or you assume he will know how to alter this/come up with another method for dealing with future clubs.

ypercubeᵀᴹ Over a year ago

@jimdrang What are you talking about? I'm only assuming he wants to do what he describes in the question.

markA Over a year ago

I think you should reread the question then unless it has changed and is not updating for me "can someone tell me how to create a loop so that I can insert the two rows, set the @clubid + 1 and then loop back again?

|

Collectives™ on Stack Overflow

Add rows to a table then loop back and add more rows for a different userid

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related