How to insert loop value in SQL Server insert-select query?

Question

I have three tables:

ORSeries where the last official receipt is temporarily saved:

|ORSeries|
+--------+
| 1000   |

Collections where users issued with an official receipt will be saved:

|   OR   |  ID    |  Date  |
+--------+--------+--------+
|        |        |        |

Users where users without an official receipt are saved:

|  ID    |  Date      |
+--------+------------+
|  0001  | 08-10-2019 |
|  0002  | 08-10-2019 |
|  0003  | 08-10-2018 |
|  0004  | 08-10-2018 |
|  0005  | 08-10-2018 |

I want to issue an official receipt to the users dated 08-10-2018. What I have so far:

INSERT INTO [Collections] (OR, Name, [Date])
    SELECT
        (SELECT SeriesNo + 1 FROM TempORSeries),
        Name,
        [Date]
    FROM 
        ORSeries

Expected output:

|   OR   |  ID    |    Date    |
+--------+--------+------------+
| 10001  |  0003  | 08-10-2018 |
| 10002  |  0004  | 08-10-2018 |
| 10003  |  0005  | 08-10-2018 |

However, all the users received the same official receipt number.

My query returns:

|   OR   |  ID    |    Date    |
+--------+--------+------------+
| 10001  |  0003  | 08-10-2018 |
| 10001  |  0004  | 08-10-2018 |
| 10001  |  0005  | 08-10-2018 |

How can I fix this?

The columns in the SQL code don't match the table definition or sample data. Which is correct? — Joel Coehoorn, Aug 30 '19 at 03:36

Joel Coehoorn · Answer 1 · 2019-08-30T03:47:15.443

Assuming there is some reason you couldn't use an Identity column, Sql Server 2016 and later support SEQUENCES.

You should get rid of the ORSeries table completely and either replace it with a new Sequence or convert the OR column in the Collections table to an identity. Here is the Sequence example:

CREATE SEQUENCE ORSequence START WITH {current sequence value + 1 here};

Then the INSERT statement will look like this:

Insert into [Collections] 
(
    OR,
    ID, 
    [Date]
)
SELECT
    NEXT VALUE FOR ORSequence,
    ID,
    [Date]
FROM Users WHERE Date = '20181018'

Both options get rid of the need to use a transaction to control updating the old ORSeries table — which you probably weren't doing before, and were therefore at risk of two sessions using the same OR numbers.

score 0 · Answer 2 · edited Aug 30 '19 at 04:17

You can use row_number + SeriesNo

begin transaction;

declare @SeriesNo int = (case 
                            when not exists(select 1 from Collections) 
                               then (select SeriesNo from TempORSeries)
                               else (select max([OR]) from Collections) 
                         end);

Insert into [Collections] 
    select
        @SeriesNo + (row_number() over (order by (select 1))),
        [Name],
        [Date]
    from
        ORSeries
    where 
        [Date] = '2018-08-10';

commit;

Online Demo With SQL Server 2012 | db<>fiddle

How can I update my ORSeries table with the last issued official receipt ?

This is a new question, I recommend using auto increment concat to avoid multiple connection problem. Please read :

tsql - How do I add string with auto increment value in SQL Server? - Stack Overflow

how can I update my ORSeries table with the last issued official receipt ? and, I want to issue official receipt to 10-08-2018 only, not including year 2019. — jreloz, Aug 30 '19 at 03:09

score 0 · Answer 3 · answered Aug 30 '19 at 04:27

begin transaction

insert Collections
select t1.[OR] + row_number() over(order by t2.ID), t2.ID, t2.Date
from ORSeries t1
cross join Users t2
where t2.Date = "08-10-2019"

update ORSeries
set [OR] = (select MAX([OR]) from Collections where Date = "08-10-2019")

commit transaction

How to insert loop value in SQL Server insert-select query?

3 Answers3