SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TempORSeries
	([SeriesNo] nvarchar(6))
;
	
INSERT INTO TempORSeries
	([SeriesNo])
VALUES
	('1000')
;
1 rows affected

CREATE TABLE ORSeries
	([Name] nvarchar(6), [Date] datetime)
;
	
INSERT INTO ORSeries
	([Name], [Date])
VALUES
	('0001', '2019-08-10 00:00:00'),
	('0002', '2019-08-10 00:00:00'),
	('0003', '2018-08-10 00:00:00'),
	('0004', '2018-08-10 00:00:00'),
	('0005', '2018-08-10 00:00:00')
;
5 rows affected

CREATE TABLE Collections
	([OR] nvarchar(6), [ID] nvarchar(6), [Date] datetime)
;

declare @SeriesNo int = (SELECT SeriesNo FROM TempORSeries);
Insert into [Collections] 
SELECT
    @SeriesNo + (row_number() over (order by (select 1))),
    [Name],
    [Date]
FROM ORSeries
5 rows affected

select * from Collections


OR
ID
Date


1001
0001
10/08/2019 00:00:00

1002
0002
10/08/2019 00:00:00

1003
0003
10/08/2018 00:00:00

1004
0004
10/08/2018 00:00:00

1005
0005
10/08/2018 00:00:00

OR	ID	Date
1001	0001	10/08/2019 00:00:00
1002	0002	10/08/2019 00:00:00
1003	0003	10/08/2018 00:00:00
1004	0004	10/08/2018 00:00:00
1005	0005	10/08/2018 00:00:00