0

I need to input into a table a list of 126 Sites with 7 types of pitches. The script I have written is not right and I cannot figure out why.

What I would like to see is a column of Site_Skey 1, 1, 1, 1, 1, 1, 1 and a column of PitchType_Skey 1,2,3,4,5,6,7 then Site_Skey 2,2,2,2,2,2,2 and a column of PitchType_Skey 1,2,3,4,5,6,7 this need to be repeated 126 times.

Can you help?

Current Script:

    DECLARE @PitchType_Skey INT
    DECLARE @Site_Skey INT

    SET @PitchType_Skey = 1
    SET @Site_Skey = 1

    WHILE (@PitchType_Skey <= 882)
    WHILE (@Site_Skey <= 882)

   BEGIN
   INSERT INTO Capacity (PitchType_Skey, Site_Skey)
   SELECT (CASE WHEN @PitchType_Skey % 7 = 0 THEN 7 ELSE @PitchType_Skey % 7 END),
   (CASE WHEN @Site_Skey % 126 = 0 THEN 126 ELSE @Site_Skey % 126 END)
   SET @PitchType_Skey = @PitchType_Skey + 1
   SET @Site_Skey = @Site_Skey + 1
   END
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
wafw1971
  • 361
  • 3
  • 7
  • 17
  • Do you want to insert these values concatenated so that `1, 1, 1, 1, 1, 1, 1 ` is one value or these should inserted as 7 values for 7 rows? – Mahmoud Gamal Feb 12 '13 at 12:32
  • correct me if I am wrong, but are you expecting 7 rows in teh table for each pite_skey ?? – SQLGuru Feb 12 '13 at 12:33
  • The Number 1 represents a Site_Skey (a foreign key within this table which is linked to the Site Table) so I would assume I would need to see the number 1 7 times and the folloing coloumn 1,2,3,4,5,6,7. – wafw1971 Feb 12 '13 at 12:36

1 Answers1

0

much simpilar way of doing it....

DECLARE @PitchType_Skey INT
DECLARE @Site_Skey INT

SET @PitchType_Skey = 1
SET @Site_Skey = 1



WHILE (@Site_Skey < 127)
BEGIN
  IF @PitchType_Skey = 8
  BEGIN
   SET @PitchType_Skey = 1
   SET @Site_Skey = @Site_Skey + 1
  END

  IF (@Site_Skey < 127)
  BEGIN
     INSERT INTO dbo.Capacity (PitchType_Skey, Site_Skey)
     SELECT @PitchType_Skey, @Site_Skey
  END

  SET @PitchType_Skey = @PitchType_Skey + 1

END
SQLGuru
  • 1,099
  • 5
  • 14