While loop in sql

• caojunhe24

  Ten Centuries

  Points: 1334

  More actions

  April 21, 2015 at 9:50 am

  #303203

  Hi all,

  I want to insert data(month&year)from 2014 till now - into temp table using 2 while loop.

  drop table #loop

  create table #loop

  (

  seq int identity(1,1),

  [month] smallint,

  [Year] smallint

  )

  Declare @year int=2014

  Declare @Month int = 1

  Declare @Currentyear int =(select year(getdate()))

  Declare @Currentmonth int =(select month(getdate()))

  While (@year <= @Currentyear)

  Begin

  While ((@Month <= @Currentmonth and @year= @Currentyear )) or (@Month <= 12 and @year< @Currentyear )

  Begin

  insert into #loop([month] ,[year]

  values(@Month,@year)

  set @Month = @Month +1

  End

  set @year = @year +1

  End

  select * from #loop

  For some reason I cant not get 2015 data .

  Anyone have an idea why ?

  Thanks

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  April 21, 2015 at 11:01 am

  #1792490

  This is a job for a Tally Table! Read this article and it will change your life:

  The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

  Using a tally table you can accomplish this like so:

  DECLARE @year int=2014;

  WITH

  YR(D) AS (SELECT CAST('1/1/'+CAST(@year AS char(4)) AS date)),

  L1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N)),

  iTally(N) AS

  (

  SELECT 0 UNION ALL

  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM L1 a, L1 b, L1 c

  )

  SELECT

  SequenceNbr = N+1,

  MonthNbr = MONTH(DATEADD(MONTH,N,D)),

  YearNbr = YEAR(DATEADD(MONTH,N,D))

  FROM iTally

  CROSS JOIN YR

  WHERE N <= DATEDIFF(MONTH,D,GETDATE());

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• caojunhe24

  Ten Centuries

  Points: 1334

  More actions

  April 21, 2015 at 12:17 pm

  #1792523

  the problem I was facing was that month is not reset after the second loop run.

  so month should be reset in the first loop before second loop starts.

  While (@year <= @Currentyear)

  set @month = 1

  Begin

  While ((@Month <= @Currentmonth and @year= @Currentyear )) or (@Month <= 12 and @year< @Currentyear )

  Thank you for your help tho

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 21, 2015 at 12:32 pm

  #1792533

  You don't need a while loop or a nested while loop to accomplish the task you are attempting to complete. Alan.B has shown you a much better way to accomplish your task.

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  April 21, 2015 at 12:35 pm

  #1792537

  caojunhe24 (4/21/2015)

  ---

  the problem I was facing was that month is not reset after the second loop run.

  so month should be reset in the first loop before second loop starts.

  While (@year <= @Currentyear)

  set @month = 1

  Begin

  While ((@Month <= @Currentmonth and @year= @Currentyear )) or (@Month <= 12 and @year< @Currentyear )

  Thank you for your help tho

  Glad you solved your problem. I would, however, seriously consider reading the article I posted and begin developing SQL that does not use loops. You can make your queries thousands of times faster by switching to a set-based approach to problems like this (something I learned from people like Lynn)

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001