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.

1. create temp table. 2. fill it with items. 3. create cursor on the table. 4. iterate using the cursor. — MarcinJuraszek
– MarcinJuraszek, Commented Jan 3, 2014 at 21:18
SQL Server doesn't haveFOR EACH, it has DO WHILE:Clicky But there are probably better ways to handle this than a loop, or cursors. — Andrew
– Andrew, Commented Jan 3, 2014 at 21:19
Where does this list come from? Your best bet is to start 'thinking in sets' rather than iteration. If your list came from a query, you could easily insert the result into another table — n8wrl
– n8wrl, Commented Jan 3, 2014 at 21:19
Why do you need to use a loop at all? Tell us what you're really doing inside the loop - usually this is the worst way to achieve something in SQL. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 3, 2014 at 21:32
@n8wrl these are values that were passed to me manually, ie. via email — Antarr Byrd
– Antarr Byrd, Commented Jan 3, 2014 at 21:55

Aaron Bertrand · Accepted Answer · 2014-01-03 22:27:13Z

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

answered Jan 3, 2014 at 22:27

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

4 Comments

Antarr Byrd Over a year ago

What does the Where type = 'P' represent on line 7?

Aaron Bertrand Over a year ago

@AntarrByrd it limits the output to the sequence of unique numbers belonging to stored procedures. If you don't filter this way, you can get multiple values of 1, multiple values of 2, etc. Compare SELECT TOP (10) number FROM master.dbo.spt_values; with SELECT TOP (10) number FROM master.dbo.spt_values WHERE type = 'P';

Aaron Bertrand Over a year ago

You could accomplish something similar using ROW_NUMBER() instead of the actual value, but that is more code from the start, then you also have to subtract 1 to get 0 as part of the result...

Aaron Bertrand Over a year ago

LINQ is terrible and definitely teaches you the wrong things about databases.

zblago · Accepted Answer · 2014-01-03 21:24:11Z

1

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

answered Jan 3, 2014 at 21:24

zblago

1621 gold badge1 silver badge10 bronze badges

Comments

Erick de Vathaire · Accepted Answer · 2024-07-15 16:39:14Z

0

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

answered Jul 15, 2024 at 16:39

Erick de Vathaire

1732 silver badges8 bronze badges

Comments

Dave Zych · Accepted Answer · 2014-01-03 21:20:36Z

-1

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

edited Jan 3, 2014 at 21:20

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

answered Jan 3, 2014 at 21:18

crs0794

912 silver badges6 bronze badges

Collectives™ on Stack Overflow

ForEach Loop in SQL Server

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related