ForEach Loop in SQL Server

Question

I have a list of values that, i.e.

in ('1xxx','12xx','21xx','98xx','00xx')

I want to use for an insert script. How can a write a for loop in SQL Server using each value within the loop? I'm think something like.

For value in ('1xxx','12xx','21xx','98xx','00xx')
  select value
endloop;

Im trying to simply this

    INSERT INTO [dbo].[TimeCard]
    VALUES
            ('test'+Right(NewId(),12),'6121126800','5102289289',CONVERT(DATE,'01-01-2013'),CONVERT(DATE,'01-01-2013'),20,CURRENT_TIMESTAMP,NULL )
    GO

    INSERT INTO [dbo].[TimeCard]
    VALUES
            ('test'+Right(NewId(),12),'6121126800','5102289289',CONVERT(DATE,'01-08-2013'),CONVERT(DATE,'01-08-2013'),20,CURRENT_TIMESTAMP,NULL)
    GO


    INSERT INTO [dbo].[TimeCard]
    VALUES
            ('test'+Right(NewId(),12),'6121126800','5102289289',CONVERT(DATE,'01-15-2013'),CONVERT(DATE,'01-15-2013'),20,CURRENT_TIMESTAMP,NULL )
    GO
....

I have to insert these records for several testing scenarios.

Answer 1

You don't need expensive loops, cursors or functions to build a set from these values you've been handed manually.

DECLARE @start DATE = '20130101', @now DATETIME2(7) = CURRENT_TIMESTAMP;

;WITH months AS 
(
  -- we need 12 months
  SELECT TOP (12) m = number FROM master.dbo.spt_values 
  WHERE type = 'P' ORDER BY number
),
-- we need a week in each month, starting at the 1st
weeks AS (SELECT w FROM (VALUES(0),(1),(2),(3)) AS w(w)),
dates AS 
(
  -- this produces a date for the first 4 weeks of each
  -- month from the start date
  SELECT d = DATEADD(WEEK,w.w,DATEADD(MONTH,m.m,@start)) 
  FROM months AS m CROSS JOIN weeks AS w
),
vals AS 
(
  -- and here are the values you were given
  SELECT v FROM (VALUES('1xxx'),('12xx'),('21xx'),('98xx'),('00xx')) AS v(v)
)
-- INSERT dbo.TimeCard(column list here please)
SELECT 
  'Test' + RIGHT(NEWID(),12),
  '6121126800',
  vals.v,
  dates.d,
  dates.d,
  20,
  @now,
  NULL
FROM dates CROSS JOIN vals
ORDER BY vals.v,dates.d;

This should return 240 rows (12 months * 4 weeks * 5 values as supplied in your question). When you've manipulated the output to be what you expect, uncomment the INSERT (but please get in the habit of putting a column list there).