How to avoid While Loop in Sql

Question

I know I should try to avoid while loop in sql but I could not get it done, can someone please suggest a better way to achieve same result without using while loop. Here is my code -

begin transaction

declare @count int;
set @count = 1;

declare @Id bigint;
set @Id = 3781543256;

while @count <=35000

begin

INSERT INTO [Table_name]
           ([Column1]
           ,[Column2]
           ,[Column3]
           ,[Column4]
           ,[Column5])
     VALUES
           (7,
           @Id,
           '20130909 16:42:43.157',
           0,
           NEWID())

set @Id = @Id+1; 

set @count = @count+1;
end

Basically - the avoidance of "loop" in SQL is if something needs to run/query often and perform well. If it is something you run rarely or "once in a while to set up data", it is okay to have loops - and in this case, I hope it's not something you'll need to run often, so why not just keep the loop? — Allan S. Hansen
– Allan S. Hansen, Commented Jun 25, 2014 at 9:06
@Jesuraja I have edited my question, actually -@nodeID is Id. — user3581461
– user3581461, Commented Jun 25, 2014 at 9:09
@user3581461 The proposed edit was rejected by 3 auditers so it didn't go through. I went in an re-added your changes. Can you verify that I did not mess it up? — ivarni
– ivarni, Commented Jun 25, 2014 at 9:40
@ivarni Can you please confirm which changes you wanted in it? — user3581461
– user3581461, Commented Jun 26, 2014 at 7:34

usr · Accepted Answer · 2014-06-25 09:08:11Z

3

You need a numbers table. It is handy to have around anyway. Assuming you have it, you can generate the set of rows you want to insert and do it in one go:

INSERT ...
SELECT 7, Numbers.ID - 1 + 3781543256, ..., NEWID()
FROM Numbers
WHERE Numbers.ID BETWEEN 1 AND @count

Shorter, less error prone and faster. Depending on the row count and schema this can be faster by multiple orders of magnitude.

answered Jun 25, 2014 at 9:08

usr

172k38 gold badges251 silver badges380 bronze badges

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

Comments

ivarni · Accepted Answer · 2014-06-25 09:39:38Z

1

INSERT INTO [Table_name] (
    [Column1]
   ,[Column2]
   ,[Column3]
   ,[Column4]
   ,[Column5]
)
SELECT TOP 35000
       7
      ,@Id + ROW_NUMBER() OVER (ORDER BY objects1.object_id)
      ,'20130909 16:42:43.157'
      ,0
      ,NEWID()
FROM sys.objects AS objects1
     CROSS JOIN sys.objects AS objects2

edited Jun 25, 2014 at 9:39

ivarni

17.9k17 gold badges79 silver badges94 bronze badges

answered Jun 25, 2014 at 9:09

adrianm

14.8k6 gold badges60 silver badges110 bronze badges

2 Comments

MP24 Over a year ago

Just out of interest: what is the CROSS JOIN for?

adrianm Over a year ago

@MP24, just an ansi version of a comma in this case (i.e. cartesian product). I just think it is more readable to explicitly say CROSS JOIN. Or did you mean why it is there at all? sys.objects usually contains a couple of thousand rows. The CROSS join ensures we get at least 37000 rows

James S · Accepted Answer · 2014-06-25 09:41:56Z

Here is an example using a recursive cte to get a list of 35000 rows...

WITH cte1 AS 
(
    SELECT 1 AS id
    UNION ALL SELECT id + 1 FROM cte1
    WHERE id < 1000 
),
cte2 AS 
(
    SELECT id FROM cte1 
    UNION ALL
    SELECT id + 1000 FROM cte2
    WHERE cte2.id <= 34000 --(the number of rows you want - 1000)
)
INSERT INTO [Table_name]
           ([Column1]
           ,[Column2]
           ,[Column3]
           ,[Column4]
           ,[Column5]
           ,[Column6]
           ,[Column7]
           ,[Column8])
SELECT
    7,
    @id + id,
    1.1,
    0,
    '20130909 16:42:43.157',
    '20130910 16:42:43.157',
    0,
    NEWID()
FROM cte2 
ORDER BY id
OPTION (MAXRECURSION 1000)

Note - split into 2 CTES as you can only have a maximum recursion limit of 32767 in a single cte. The default recursion limit is 100 - hence the OPTION (MAXRECURSION 1000)

I've done a quick test of the SELECT part of this, and it is very fast - less than a second.

Collectives™ on Stack Overflow

How to avoid While Loop in Sql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related