CODEWITHSUNDEEP
sqlforeachsql-server-2012iteration
Antarr Byrd
Antarr Byrd

Reputation: 26179

ForEach Loop in SQL Server

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.

Upvotes: 0

Views: 17694

Answers (4)

Erick de Vathaire
Erick de Vathaire

Reputation: 173

use a recursive cte for the dates and "select values" statement for those examples:

;with dates as (
        select d=convert(date, '2024-01-01')
    union all
        select DATEADD(day, 7, d)
        from dates
        where d < getdate()
    )
select
    'test'+Right(NewId(),12)
    ,v
    ,d
    ,d
    ,20
    ,CURRENT_TIMESTAMP
    ,NULL
from dates d
cross join (SELECT v FROM (VALUES('1xxx'),('12xx'),('21xx'),('98xx'),('00xx')) AS v(v)) v

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280643

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).

Upvotes: 1

zblago
zblago

Reputation: 152

If you have comma delimited string use some of these 4 functions that returns table (http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx). Insert returned data in temp table with identity column (1,1).

After that loop through table with cursor or using previously created identity column. http://technet.microsoft.com/en-us/library/ms178642.aspx

Upvotes: 1

crs0794
crs0794

Reputation: 91

I think you can use a cursor, but you'll need to put this ('1xxx','12xx','21xx','98xx','00xx')

in something like this

select '1xxx','12xx','21xx','98xx','00xx'

More information about cursor:

http://technet.microsoft.com/pt-br/library/ms180169.aspx

Upvotes: -1

Related Questions