How to generate consecutive numbers in string column in SQL Server

Question

I have to generate data in a table with specific string column of consecutive numbers appended.

For each branch I have to insert 3 users with consecutive numbers appended (like user1, user2, user3). Challenge here is I have to generate the branch string also with consecutive number appended (like branch001,branch002, branch003, up to branch700)

For example, below is the sample structure,

create table usersTable 
(
    id int identity(1,1),
    users nvarchar(100),
    branch nvarchar(100)
)

Expected output:

id    users    branch
-----------------------
1     user1    branch1
2     user2    branch1
3     user3    branch1
4     user4    branch2
5     user5    branch2
6     user6    branch2
......    
n     usern    branch700
n     usern    branch700
n     usern    branch700

Can anyone please advice how to achieve this in SQL Server? Thanks in advance

Do you have a _numbers table_ (aka _tally table_) at your disposal? A `cross join` would make quick work of this problem. — HABO, Jul 09 '19 at 15:12
I don't have right now but it can be created, but I request any code here to achieve this. — Mar1009, Jul 09 '19 at 15:15
I can see the expected result, but not the starting data. How doesit look like? — The Impaler, Jul 09 '19 at 15:16
You can find code to create a tally table at runtime [here](https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes) — Brian, Jul 09 '19 at 15:16

Zohar Peled · Accepted Answer · 2019-07-09T15:50:37.817

Update

To get an increasing number of users for all branches, use row_number():

INSERT INTO usersTable (users, branch)
SELECT CONCAT('User', ROW_NUMBER() OVER(ORDER BY t1.number, t0.number)), 
       CONCAT('branch', t1.number)
FROM tally t0
CROSS JOIN tally t1
WHERE t0.number <= 3
AND t1.number <= 700
ORDER BY t1.number, t0.number

Original version

If you already have a tally (numbers) table, a simple insert...select using cross join and concat can get you the desired data:

INSERT INTO usersTable (users, branch)
SELECT CONCAT('User', t0.number), CONCAT('branch', t1.number)
FROM tally AS t0
CROSS JOIN tally AS t1
WHERE t0.number <= 3
AND t1.number <= 700

If you don't already have a tally table, read this SO post on ways to create it.
You can also read this database administrators post on why you actually want one.

If You can't or don't want to create a tally table, you can use a common table expression to generate a tally table on the fly - there are several ways to generate it - Gordon already shown the recursive cte approach, so I'll show a different one - using row_number and cross join:

With tally as
(
    SELECT TOP 700 ROW_NUMBER() OVER(ORDER BY @@SPID) As number
    FROM sys.objects a
    CROSS JOIN sys.objects b
)

INSERT INTO usersTable (users, branch)
SELECT CONCAT('User', t0.number), CONCAT('branch', t1.number)
FROM tally AS t0
CROSS JOIN tally AS t1
WHERE t0.number <= 3
AND t1.number <= 700

thanks Zohar, excuse me for the change in requirement, I need the users not be limited up to 3 but I need to increment it up to consecutive number through out the result set, for example branch 1 will have users1,2,3 and branch 2 will have users 4,5,6 and up to branch 700. please advice — Mar1009, Jul 09 '19 at 15:48
Refresh the page. I've already updated my answer with a solution for this as well. — Zohar Peled, Jul 09 '19 at 15:50
I replaced the user cancat with this - CONCAT('User', ROW_NUMBER() OVER(ORDER BY t1.number, t0.number)) and I get what I need. many thanks for the solution. — Mar1009, Jul 09 '19 at 15:55

Gordon Linoff · Answer 2 · 2019-07-09T15:53:27.890

2

This answers the original version of the question.

You can generate the data using a recursive CTE:

with u as (
      select v.users
      from (values ('user1'), ('user2'), ('user3')) v(users)
     ),
     n as (
      select 1 as n
      union all
      select n + 1
      from n
      where n < 700
     ) 
select u.users, concat('branch', n.n)
from u cross join
     n
order by n.n, u.users
option (maxrecursion 0);

The insert then looks like:

with u as (
      select v.users
      from (values ('user1'), ('user2'), ('user3')) v(users)
     ),
     n as (
      select 1 as n
      union all
      select n + 1
      from n
      where n < 700
     ) 
insert into usersTable (users, branch) 
    select u.users, concat('branch', n.n)
    from u cross join
         n
    order by n.n, u.users
    option (maxrecursion 0);

Here is a db<>fiddle.

edited Jul 09 '19 at 15:53

answered Jul 09 '19 at 15:16

Gordon Linoff

1,242,037
58
646
786

thanks for the prompt help, and excuse for the change in requirement I need the users also should be consecutive numbers not users up to 3, but with incremented numbers through out the result set, please can you help modify the approach, much appreciated in advance. – Mar1009 Jul 09 '19 at 15:37
@Mar1009 . . . This answers your original question. If you have a new question, it should be asked as a *new* question. – Gordon Linoff Jul 09 '19 at 15:53
yes sorry for the confusion, I managed to figure it out with both of the answers provided, many thanks for your thoughts. – Mar1009 Jul 09 '19 at 15:54

How to generate consecutive numbers in string column in SQL Server

2 Answers2