use cte instesd of while and temp table

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  November 11, 2013 at 4:50 am

  #300126

  hello all.

  I have this quey:

  IF OBJECT_ID('tempdb..#Out') IS NOT NULL

  DROP TABLE #Out

  declare @count int,@i int,@RoleID int,@UserID int

  create table #Out (ID int identity(1,1) primary key,RoleID int,UserID int)

  insert into #Out select Role_ID,UserID from Roles

  set @count=@@ROWCOUNT

  set @i=1

  while (@i<=@count)

  begin

  select @RoleID=RoleID,@UserID=UserID from #Out where ID=@i

  insert into FarzinIntro values(@RoleID,@UserID,0,GETDATE())

  insert into FarzinIntro values(@RoleID,@UserID,1,GETDATE())

  insert into FarzinIntro values(@RoleID,@UserID,2,GETDATE())

  set @i=@i+1

  end

  drop table #Out

  now i want to use cte instead of while and temp table?is it possible?if yes,how can i do this?

  thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 11, 2013 at 5:02 am

  #1665425

  A CTE is just a named subquery, it's not a table, it has no storage, it is only in scope for the immediate next statement.

  I can't see anywhere obvious where a CTE would work in that query, not sure the loop is necessary though.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 11, 2013 at 5:20 am

  #1665428

  If I read your code correctly, this may work:

  INSERT INTO FarzinIntro

  SELECT

  r.Role_ID,

  r.User_ID,

  dt1.n,

  GETDATE()

  FROM

  Roles r CROSS JOIN

  (SELECT n FROM (VALUES (0),(1),(2))dt(n))dt1

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  November 11, 2013 at 1:37 pm

  #1665571

  elham_azizi_62 (11/11/2013)

  ---

  ...now i want to use cte instead of while and temp table?is it possible?if yes,how can i do this?

  thanks

  You can do this using a recursive CTE like so:

  WITH roles_recursive AS

  (

  SELECT

  Role_ID,

  User_ID,

  0 some_id,

  GETDATE() dt

  FROM Roles

  UNION ALL

  SELECT

  r.Role_ID,

  r.User_ID,

  some_id+1,

  GETDATE() dt

  FROM roles_recursive r

  WHERE some_id<2

  )

  SELECT * FROM roles_recursive

  ORDER BY Role_ID, User_ID, some_id

  That said, I would not recommend this approach for what you are doing. The code Lynn posted is the way to go.

  "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

• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  November 11, 2013 at 2:02 pm

  #1665580

  Lynn Pettis (11/11/2013)

  ---

  If I read your code correctly, this may work:

  INSERT INTO FarzinIntro

  SELECT

  r.Role_ID,

  r.User_ID,

  dt1.n,

  GETDATE()

  FROM

  Roles r CROSS JOIN

  (SELECT n FROM (VALUES (0),(1),(2))dt(n))dt1

  +1000 😀

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  November 11, 2013 at 4:36 pm

  #1665616

  Lynn Pettis (11/11/2013)

  ---

  If I read your code correctly, this may work:

  INSERT INTO FarzinIntro

  SELECT

  r.Role_ID,

  r.User_ID,

  dt1.n,

  GETDATE()

  FROM

  Roles r CROSS JOIN

  (SELECT n FROM (VALUES (0),(1),(2))dt(n))dt1

  Also +1000, Great simplification !

  One minor thing that I can't help pointing out - call it a "cross join of my brain with pedantry"

  INSERT INTO FarzinIntro

  SELECT

  r.Role_ID,

  r.User_ID,

  dt.n,

  GETDATE()

  FROM

  Roles r CROSS JOIN

  (VALUES (0),(1),(2))dt(n)

  There doesn't seem to be any need for the extra SELECT... as dt1 round the VALUES clause...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]