Insert text concatenated with incrementing number sequence

Question

I would like to insert 1000 rows into a table. The data i would like to generate and insert is TEXT0001 up to TEXT1000. So single digits need to have 3 leading zeroes (eg 1 -> 0001), 2 digits need 2 leading zeroes (2 -> 0091) etc.

I have looked at this answer on how to generate a range of numbers without using a loop and that works, but i need to add text infront of the number.

I have tried:

INSERT INTO projects
  SELECT TOP (1000) CONCAT("TEXT",  n) = ROW_NUMBER()OVER (ORDER BY [object_id]) 
  FROM sys.all_objects ORDER BY n;

but this gives an error - incorrect syntax near =.

I have read that using a SET based approach is the best way and i should avoid looping, but no actual examples to point me in the right direction.

How would i go about doing this please?

That's not valid SQL syntax. Functions arguments are passed *inside* the parentheses, not through an assignment operator. I don't know of any language that works this way — Panagiotis Kanavos, Sep 27 '19 at 11:21

score 1 · Answer 1 · edited Sep 27 '19 at 11:43

1

i think you need below

INSERT INTO projects
  SELECT TOP (1000) "TEXT" + right('0000',cast ((ROW_NUMBER()OVER (ORDER BY [object_id])) as varchar),4)
  FROM sys.all_objects ;

edited Sep 27 '19 at 11:43

Fahmi

37,315
5
22
31

answered Sep 27 '19 at 11:18

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks @Zaynul. How would i format the generated number as 4 digits. For example instead of 1 i need 0001. – Nick Sep 27 '19 at 11:22

Gordon Linoff · Answer 2 · 2019-09-27T12:36:24.793

1

I would recommend keeping concat() and fixing the syntax:

INSERT INTO projects (<column name here>)
  SELECT TOP (1000) CONCAT('TEXT', 
                           FORMAT(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '0000')) as n
  FROM sys.all_objects
  ORDER BY n;

edited Sep 27 '19 at 12:36

answered Sep 27 '19 at 11:22

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon. Would this format single digits eg 1 as 0001? – Nick Sep 27 '19 at 11:23
Order by n means TEXT1, TEXT10, TEXT11 and so on – Salman A Sep 27 '19 at 11:25

Salman A · Accepted Answer · 2019-09-27T11:42:25.383

0

After some syntax correction and FORMAT function you have:

INSERT INTO #temp
SELECT TOP 1000 'TEXT' + FORMAT(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '0000') AS n
FROM sys.all_objects
ORDER BY n -- it is a zero-padded varchar suitable for order-by

edited Sep 27 '19 at 11:42

answered Sep 27 '19 at 11:24

Salman A

262,204
82
430
521

Insert text concatenated with incrementing number sequence

3 Answers3