1

Topic: How to fill automatically a column specifying start number and finite

I specify 1 and 10 and  with the automatic machine it is filled on
1
2
3
4
5
6
7
8
9
10

2

Re: How to fill automatically a column specifying start number and finite

kolyady;
And if lines 1000?

3

Re: How to fill automatically a column specifying start number and finite

TaPaK;
Yes though , to what a question?

4

Re: How to fill automatically a column specifying start number and finite

kolyady;
In 11 line that will be?

5

Re: How to fill automatically a column specifying start number and finite

TaPaK;
Did not understand a question?
Range with 1 on 10 (on an example) it will not be logical that 11

6

Re: How to fill automatically a column specifying start number and finite

kolyady wrote:

TaPaK;
Did not understand a question?
Range with 1 on 10 (on an example) it will not be logical that 11

As it tires
Leave

DECLARE @s INT = 1;
@f INT = 10
SELECT (ROW_NUMBER () OVER (ORDER BY 1/0) - 1) % (@F - s +1) + 1 + (@s - 1)
FROM sys.objects

7

Re: How to fill automatically a column specifying start number and finite

TaPaK;
Instead of 10 flooded 4000 lines repeating with 1 on 10

8

Re: How to fill automatically a column specifying start number and finite

kolyady wrote:

TaPaK;
Instead of 10 flooded 4000 lines repeating with 1 on 10

Well that such it is a lot of smile

9

Re: How to fill automatically a column specifying start number and finite

kolyady;
https://www.google.ru/search? q=ben-gan getnums

10

Re: How to fill automatically a column specifying start number and finite

kolyady wrote:

TaPaK;
Instead of 10 flooded 4000 lines repeating with 1 on 10

So he after all also asked, how many lines are necessary :-) I too understood that an amount of lines not important, the main thing that numbers would go from 1 to 10 in a cycle. If 10 lines add in  top 10 for example were necessary only :-) And if the normal counter in the table it is possible to declare in the field IDENTITY (1,1) is necessary.

11

Re: How to fill automatically a column specifying start number and finite

The Neva;
Let's admit is:
Start number = 1002345
Finite number = 2006589
I need to fill to the table with all numbers which in between, including them.
It is not necessary for me:
-That they repeated
That was more or less  a range
2006589-1002345 = 1004244 - an amount of lines as a result

12

Re: How to fill automatically a column specifying start number and finite

Already all prompted to you,  the ready decision

13

Re: How to fill automatically a column specifying start number and finite

*Select

14

Re: How to fill automatically a column specifying start number and finite

kolyady;
That else

15

Re: How to fill automatically a column specifying start number and finite

kolyady wrote:

Neva;
Let's admit is:
Start number = 1002345
Finite number = 2006589
I need to fill to the table with all numbers which in between, including them.
It is not necessary for me:
-That they repeated
That was more or less  a range
2006589-1002345 = 1004244 - an amount of lines as a result

WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0);
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3);
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2);
TenMillion (N) AS (SELECT 1 FROM MILLIONS CROSS JOIN TENS);
TALLY (N) AS (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT 0)) FROM TenMillion)
SELECT TOP (2006589-1002345 (1002344) + N FROM TALLY;

16

Re: How to fill automatically a column specifying start number and finite

The Neva;
Here all not that, I understand that is possible and as in examples, I am simple when that saw the decision literally in the form of 2 lines of the code, it and worked instantly and mega idle time. Function approaches besides for a small range, on 10 million it is bent.
I understand that that that I want but it is not clear that, simply hoped that who that throws off these 2 lines well or on similarity sad

17

Re: How to fill automatically a column specifying start number and finite

The sufferer;
Create the table of numbers and select on  normal

18

Re: How to fill automatically a column specifying start number and finite

kolyady;
The static table of numbers, once created and use everywhere... Faster will be nothing
Idle time but not the fastest method of creation

CREATE TABLE dbo. Numbers (N INT)
GO
INSERT INTO dbo. Numbers DEFAULT VALUES
GO 10000000

19

Re: How to fill automatically a column specifying start number and finite

kolyady wrote:

Neva;
Here all not that, I understand that is possible and as in examples, I am simple when that saw the decision literally in the form of 2 lines of the code, it and worked instantly and mega idle time. Function approaches besides for a small range, on 10 million it is bent.
I understand that that that I want but it is not clear that, simply hoped that who that throws off these 2 lines well or on similarity sad

Well at me on a normal server for the reporting your range fulfilled for a second. 10 million were interposed into the table for 11 seconds You confuses the big syntax? Save a script as function and cause its only one line :-)

20

Re: How to fill automatically a column specifying start number and finite

TaPaK wrote:

kolyady

CREATE TABLE dbo. Numbers (N INT)
GO
INSERT INTO dbo. Numbers DEFAULT VALUES
GO 10000000

Only, likely

CREATE TABLE dbo. Numbers (N INT IDENTITY)

21

Re: How to fill automatically a column specifying start number and finite

The total:
After high-speed performance anything the best did not find:
IF OBJECT_ID (N'dbo. GetNums', N'IF ') IS NOT NULL DROP FUNCTION dbo. GetNums;
GO
CREATE FUNCTION dbo. GetNums (@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D (c));
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B);
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B);
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B);
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B);
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B);
Nums AS (SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT TOP (@high - @low + 1) @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum;
GO
It is possible to close a subject

22

Re: How to fill automatically a column specifying start number and finite

kolyady, actually this function also has been written, how result of researches - what method faster. Truth at this forum couple of years there was an arguing back, and there found methods which under certain conditions  give  fast result, but it is not exact.
If it is absolutely interesting - can look on a function title at a forum, only long to search for that arguing.