CODEWITHSUNDEEP
sqlsql-serverstringsql-server-2014
Nick
Nick

Reputation: 3845

Insert text concatenated with incrementing number sequence

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?

Upvotes: 0

Views: 1095

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

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;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

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 ;

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272236

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

Upvotes: 0

Related Questions