SQL Server loop and sql insert
I need to run a loop in SQL. Google is a little tricky for some strange reason. In SQL Server, I just put this 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 set the zoom_start and zoom_end parameters to (0,0) - (14,14) just omit (0.14), as already indicated in the table.
I think I'm near. Does anyone know where this is? Thank. Cheers -Jeremy
+3
source to share
3 answers
Instead of doing a nested loop and inserting each row at a time, you can use a CTE (or table of existing numbers) 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)
Tuning operations in SQL are usually cleaner and easier to understand than procedural line-by-line.
+14
source to share
You don't reinitialize @z_end
to zero after the first loop. Make
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
+2
source to share
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)
+1
source to share