SQL Server loop and insert in sql

Question

I need to run a loop in SQL. Google this is a little hard for some odd reason. In SQL Server I just put this is in the query editor in SQL Server Managment Studio

DECLARE @z_start INT
SET @z_start = 0
DECLARE @z_end INT
SET @z_end = 0

WHILE (@z_start <= 14) BEGIN
    IF(@z_start != 0 AND @z_end != 14)
        WHILE (@z_end <= 14) BEGIN
            INSERT INTO [campusMap].[dbo].[zoom_levels] ([zoom_start],[zoom_end]) VALUES (@z_start,@z_end)
            SET @z_end = @z_end + 1
        END
        SET @z_start = @z_start + 1
    END
END

All I want to do is to set the zoom_start and zoom_end so (0,0) to (14,14) skipping only (0,14) as that is already in the table.

I think I'm close. Anyone know where it's off? Thank you. Cheers -Jeremy

Michael Fredrickson · Accepted Answer · 2012-03-26 16:35:04Z

14

Instead of doing a nested loop and inserted each row at a time, you can use a CTE (Or a pre-existing numbers table) and add all the rows you need at once:

;WITH Numbers (Number) AS 
(
    SELECT 0 
    UNION ALL
    SELECT 1 + Number FROM Numbers 
    WHERE 1 + Number <= 14
)
INSERT INTO [campusMap].[dbo].[zoom_levels] ([zoom_start],[zoom_end])
SELECT n1.Number, n2.Number 
FROM
    Numbers n1 CROSS JOIN
    Numbers n2
WHERE
    NOT (n1.Number = 0 AND n2.Number = 14)

Set-based operations in SQL are usually cleaner and easier to understand than procedural, row-by-row approaches.

answered Mar 26, 2012 at 16:35

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

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

Comments

Lamak · Accepted Answer · 2012-03-26 16:33:18Z

2

You are not reinitializing @z_end to zero after the first loop. Do

DECLARE @z_start INT
SET @z_start = 0
DECLARE @z_end INT
SET @z_end = 0
WHILE (@z_start <= 14) BEGIN
    IF(@z_start != 0 AND @z_end != 14)
        WHILE (@z_end <= 14) BEGIN
            INSERT INTO [campusMap].[dbo].[zoom_levels] ([zoom_start],[zoom_end]) VALUES (@z_start,@z_end)
            SET @z_end = @z_end + 1
        END
        SET @z_start = @z_start + 1
        SET @z_end = 0
    END
END

answered Mar 26, 2012 at 16:33

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Comments

Mikael Eriksson · Accepted Answer · 2012-03-26 16:48:51Z

1

insert into [campusMap].[dbo].[zoom_levels] ([zoom_start],[zoom_end])
select T1.N, T2.N
from 
  (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14)) as T1(N)
cross join
  (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14)) as T2(N)
where not (T1.N = 0 and T2.N = 14)

answered Mar 26, 2012 at 16:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

SQL Server loop and insert in sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related