CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3581461
user3581461

Reputation: 55

How to avoid While Loop in Sql

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

Upvotes: 1

Views: 3450

Answers (3)

James S
James S

Reputation: 3588

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.

Upvotes: 0

adrianm
adrianm

Reputation: 14726

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

Upvotes: 1

usr
usr

Reputation: 171178

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.

Upvotes: 3

Related Questions