INSERT/SELECT question

Transact-SQL
1

As part of our system we have 2 tables, Program and Tuition, Program has a TuitionID foreign key.

Program Table:

ProgramID Year TuitionID Name

1 2021 1   Program1
2 2021 11 Program2
3 2021 12 Program3

Tuition Table:

TuitionID Year   Name
1         2021  Standard
11        2021  Advanced
12        2021  Deluxe

For the 2022 year we copy the rows for the Tuition and Program tables to allow editing of the records for that specific year

So Tuition table becomes:

TuitionID Year   Name
1          2021  Standard
11        2021  Advanced
12        2021  Deluxe
13        2022  Standard
14        2022  Advanced
15        2022  Deluxe

To prepare the Program table for 2022 I'd like run this query:

INSERT INTO dbo.Program
SELECT 
	2022, 
	-- ?? TuitionID for 2022
    ,Name
FROM Program WHERE Year = 2021

How do I get the new Tuition ID for 2022 for each Program record? e.g Program1 in 2021 has TuitionID 1 but for 2022 it would be TuitionID 13. Is it possible to do this with this INSERT/SELECT query?

Thank you.

2

You can try this, but keep in mind it won't add any records where the names don't match

drop table if exists #tuition 
drop table if exists #Program 


Create table #Program (ProgramID int identity(1,1), Year smallint, TuitionID int, Name varchar(10))
insert into #Program 
values
 (2021,1 ,'Program1')
,(2021,11,'Program2')
,(2021,12,'Program3')


create table #tuition(TuitionID int, Year smallint,  Name varchar(10))
insert into #Tuition values
 (1 ,2021,'Standard')
,(11,2021,'Advanced')
,(12,2021,'Deluxe')
,(13,2022,'Standard')
,(14,2022,'Advanced')
,(15,2022,'Deluxe')

insert into #program
Select t22.year, t22.tuitionid, t22.name
  from #Program p
	join #tuition t
		on p.tuitionId = t.tuitionid
    join #tuition t22
		on t.year = t22.year - 1
		and t.name = t22.name

		select * from #Program
3

Mike01, thanks for the input. If I run the code you provided the #Program table looks like:

1 2021 1 Program1
2 2021 11 Program2
3 2021 12 Program3
4 2022 13 Standard
5 2022 14 Advanced
6 2022 15 Deluxe

Records 4,5,6 the Name is the Tuition name - it should be the Program name.

4

I came up with this:

INSERT INTO #program
SELECT 
	2022, 
	(
		select t.TuitionID from #Tuition t
		inner join (
			select t.Name as TuitionName
			from #program p 
			inner join #Tuition t on p.TuitionID = t.TuitionID
			where t.Year = 2021 
			AND p.ProgramID = pt.ProgramID
			) AS TU
		ON t.Name = TU.TuitionName
		WHERE t.Year = 2022
	),
	[Name]

FROM #program pt
WHERE pt.Year = 2021

select * from #Program
5

you could just change this

insert into #program
Select t22.year, t22.tuitionid, p.name
from #Program p
join #tuition t
on p.tuitionId = t.tuitionid
join #tuition t22
on t.year = t22.year - 1
and t.name = t22.name