Migrating table values to another Database table in SQL Server

I'm building a new system for a company.

They already have a system and the database is SQL Server.

Because of the issues I currently have in the existing system, I'm developing a new web-based application. Here in the new database, I have slightly changed the database table structures.

So now I want to migrate the existing data to the new table and need help to develop a script for that.

The issue is the existing system has these two tables which hold the Country and Province records.

Tables are Nations and Provinces

I have joined them and these are the query

SELECT  P.Name, 
        P.Code1,
        P.IDNation,
        N.IDNation, 
        N.Code1
  FROM [MondoErp-UAT].[dbo].[Provinces] P
  LEFT JOIN Nations N ON P.IDNation = N.IDNation

This is the new Country table

  SELECT NC.Country_Name
  FROM [Mondo-UAT].[dbo].[Countries] NC
  order by NC.Country_Name

So in my new database table, I already migrated the Nations data to the Country table. But the new database Country table and the old database Nations Ids are different.

So I want to create a query to Check the old database Nation Name with the new Database Country Name and then get them New table Id and related Province Name from the old table. And then I can insert the temporary table into my new structured table.

I hope I described well my matter. This is the first time I'm trying to do this kind of migration. So any sample I can do the rest of my work.

As for a summary of my quiz, I want to first check the old database Country Name with the new database Country Name and get the new Id and get the related province name from the old database and create a temporary table and then from that table, I can insert it into the new table.

This is I tried, but I can't figure out how to end this, I think I have to use For loop or something

Please note this is a incomplete script. I just share it with you to get an idea about this.

Create PROCEDURE ProvinceMigration
@OldProvinceName	varchar(50)	=null,
@NewCountryName	varchar(50)	=null,
@OldCountryId	int	=null,
@NewCountryId	int	=null
 
AS
BEGIN
DECLARE @Temp 
TABLE(
NewCountryId INT,
OldProvinceName	varchar(50)
)


BEGIN
	SET @NewCountryId =(SELECT c.id FROM [Mondo-UAT].dbo.Countries c ,[MondoErp-UAT].dbo.Nations n  WHERE n.Code1 = c.Country_Name)
	SET @OldProvinceName = (SELECT c.id FROM [Mondo-UAT].dbo.Countries c ,[MondoErp-UAT].dbo.Nations n  WHERE n.Code1 = c.Country_Name)

	INSERT INTO @Temp(NewCountryId,OldProvinceName) VALUES (@NewCountryId
END