Populate table with some gaps in between

Question

Post reply

Populate table with some gaps in between

inHouseDBA

Default port

Points: 1403
More actions
March 28, 2015 at 6:02 pm

#398257

Hello Gurus,
I need to populate a table with numbers with some gaps in between with logic below.
first row -1110
last row - 9550
1110
1120
1130
1140
1150
1210
1220
1230
1240
1250
1310
1320
1330
1340
1350
1410
1420
1430
1440
1450
1510
1520
1530
1540
1550
2110
2120
2130
2140
2150
2210
2220
2230
2240
2250
2310
2320
2330
2340
2350
2410
2420
2430
2440
2450
2510
2520
2530
2540
2550
3110
3120
3130
3140
3150
..................
9510
9520
9530
9540
9550
Any suggestions, ideas?
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63499 More actions · Answer 1

CREATE TABLE Tally2 (n INT

CONSTRAINT Tally2PK PRIMARY KEY (n));

GO

DECLARE @num INT = 111

WHILE @num <=955

BEGIN

INSERT INTO Tally2(n) VALUES (@num);

SET @num = @num + 1;

END

GO

inHouseDBA Default port Points: 1403 More actions · Answer 2

Thank you,

Number increments from 1110 till 1150 and then it starts again from 1210 till 1250, etc....

Once it reaches 1550 (2550/3550.../8550) it jumps to next thousand:

1550 -->2110

2550-->3110

3550-->4110

.....

8550-->9110

*****************

1110

1120

1130

1140

1150

1210

1220

1230

1240

1250

1310

1320

1330

1340

1350

1410

1420

1430

1440

1450

1510

1520

1530

1540

1550

2110

2120

2130

2140

2150

2210

2220

2230

2240

2250

2310

2320

2330

2340

2350

2410

2420

2430

2440

2450

2510

2520

2530

2540

2550

3110

3120

3130

3140

3150

..................

9510

9520

9530

9540

9550

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Quick and simple solution, the problem is trivial if broken down into iteration by the different orders of magnitude, that is 1000 => 1 - 9, 100 => 1 - 5, 10 => 1 - 5. All that's left is then to get the Cartesian produce.

😎

USE tempdb;

GO

SET NOCOUNT ON;

/* Building a sequence of numbers by the following rules

1. 1000 - 9000 k1

2. 100 - 500 h1

3. 10 - 50 d1

First element is N = k1 + h1 + d1

*/

DECLARE @FIRST_ITER INT = 9; /* 1000 */

DECLARE @SECOND_ITER INT = 5; /* 100 */

DECLARE @THIRD_ITER INT = 5; /* 10 */

;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

, FIRST_ITER(N) AS (SELECT TOP(@FIRST_ITER) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1 /* ,T T2,T T3,T T4,T T5,T T6 */ )

,SECOND_ITER(N) AS (SELECT TOP(@SECOND_ITER) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1 /* ,T T2,T T3,T T4,T T5,T T6 */ )

, THIRD_ITER(N) AS (SELECT TOP(@THIRD_ITER) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1 /* ,T T2,T T3,T T4,T T5,T T6 */ )

SELECT

(FI.N * 1000) + (SI.N * 100) + (TI.N * 10) AS NUM_OUT

FROM FIRST_ITER FI

CROSS APPLY SECOND_ITER SI

CROSS APPLY THIRD_ITER TI

ORDER BY FI.N

,SI.N

,TI.N

;

Results

NUM_OUT

--------

1110

1120

1130

1140

1150

1210

1220

1230

1240

1250

1310

1320

1330

1340

1350

1410

1420

1430

1440

1450

1510

1520

1530

1540

1550

2110

2120

2130

2140

2150

2210

2220

2230

2240

2250

2310

2320

2330

2340

2350

2410

2420

2430

2440

2450

2510

2520

2530

2540

2550

3110

3120

3130

3140

3150

3210

3220

3230

3240

3250

3310

3320

3330

3340

3350

3410

3420

3430

3440

3450

3510

3520

3530

3540

3550

4110

4120

4130

4140

4150

4210

4220

4230

4240

4250

4310

4320

4330

4340

4350

4410

4420

4430

4440

4450

4510

4520

4530

4540

4550

5110

5120

5130

5140

5150

5210

5220

5230

5240

5250

5310

5320

5330

5340

5350

5410

5420

5430

5440

5450

5510

5520

5530

5540

5550

6110

6120

6130

6140

6150

6210

6220

6230

6240

6250

6310

6320

6330

6340

6350

6410

6420

6430

6440

6450

6510

6520

6530

6540

6550

7110

7120

7130

7140

7150

7210

7220

7230

7240

7250

7310

7320

7330

7340

7350

7410

7420

7430

7440

7450

7510

7520

7530

7540

7550

8110

8120

8130

8140

8150

8210

8220

8230

8240

8250

8310

8320

8330

8340

8350

8410

8420

8430

8440

8450

8510

8520

8530

8540

8550

9110

9120

9130

9140

9150

9210

9220

9230

9240

9250

9310

9320

9330

9340

9350

9410

9420

9430

9440

9450

9510

9520

9530

9540

9550

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

SELECT [Out] = t.n+h.n+d.n

FROM (VALUES (1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t (n)

CROSS JOIN (VALUES (100),(200),(300),(400),(500)) h (n)

CROSS JOIN (VALUES (10),(20),(30),(40),(50)) d (n)

ORDER BY [Out];

WITH rs AS (SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) d (n))

SELECT [Out] = (t.n*1000)+(h.n*100)+(d.n*10)

FROM rs t CROSS JOIN rs h CROSS JOIN rs d

WHERE h.n < 6 AND d.n < 6

ORDER BY [Out];

Oops...should have looked before posting. Same as Eirikur's.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

barsuk SSChampion Points: 12280 More actions · Answer 5

barsuk

SSChampion

Points: 12280

March 29, 2015 at 1:48 am

#1787150

Thanks, nice solution

inHouseDBA Default port Points: 1403 More actions · Answer 6

Gurus,

Thank you very much for your help.

Is there a way you can explain the interaction of

WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

with the rest of the solution.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

inHouseDBA (3/29/2015)
Gurus,
Thank you very much for your help.
Is there a way you can explain the interaction of
WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
with the rest of the solution.

It just creates 10 rows and it doesn't actually matter what those rows contain except that they should be short for performance reasons. The NULL doesn't really mean anything here. It could be 1s, 0s, anything. All it's doing is building "the presence of rows" that will be used in cross joins to build more rows instead of using a much slower loop or much slower recursive CTE.

--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)

inHouseDBA Default port Points: 1403 More actions · Answer 8

inHouseDBA

Default port

Points: 1403

March 30, 2015 at 2:07 am

#1787228

Thank you