Migrating Data table values to Another Database Table using Script

Question

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 Id's 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 the 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 my incomplete script, I just share it with you to get an idea for you, I think need to use For loop or something here, but no skills.


USE [Mondo-UAT]
USE [MondoErp-UAT]

GO
/****** Object:  StoredProcedure [dbo].[RPT_JobMonitor_Workshop]    Script Date: 9/27/2022 4:42:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

Does your new Countries table have a primary key that isn't a country name? If not, stop right now. Every table needs a primary key, and usually this should not a description or label field. — Nick.Mc, Sep 28 '22 at 03:32
Please check using Cursor for looping through. https://www.c-sharpcorner.com/article/cursors-in-sql-server/ — donstack, Sep 28 '22 at 04:02
@Nick.McDermaid Hi. Yes, it has a primary key. The issue is the IDs are different for both tables and the ``old DB province table`` has the ``old DB country Id`` and related ``Province Name``. So that's why I want to match the ``Country Name`` with the ``old and new DB`` Country tables (Example: Check the ``Old Country Table Country Name`` with the ``New table`` and get the ``New Id`` and check the ``Old province table`` which related ``Old country table Id`` with ``Province table Country Id`` and assign it with the new country Id. And then Insert it into the New table. — Dev Beginner, Sep 28 '22 at 04:17
So is it fair to say you want to match `[Mondo-UAT].[dbo].[Countries].Country_Name` to `[MondoErp-UAT].[dbo].Nations.Code1`? What you are asking definitely does not require loops. But I need a clear explanation of what maps to what — Nick.Mc, Sep 28 '22 at 05:17
@Nick.McDermaid Yes. Because in the Old Database Table ``Name`` column they included the country name in different Language. In the ``Code1`` column has the English Name. That's why need to check with the ``Country Name`` with ``Nations Code1`` — Dev Beginner, Sep 28 '22 at 05:35

score 1 · Answer 1 · answered Sep 28 '22 at 05:52

@Nick.McDermaid, @donstack

Thanks for the support you have given me. I Finally figure out a way of doing this. This is the way I have done and I'm posting it here if in the future anyone got the same scenario, he will be gonna need this.

INSERT INTO [Mondo-UAT].dbo.Provinces (
    [Province_Name],
    [Province_Code],
    [Country_Id],
    [Note],
    [Status],
    [CreatedDate],
    [CreateBy],
    [ModifiedDate],
    [ModifiedBy]
    )
SELECT 
p.Name,
P.Code1,
C.Id,
'N/A',
1,
SYSDATETIME(),
1,
SYSDATETIME(),
1
FROM [MondoErp-UAT].dbo.Provinces P
LEFT JOIN [MondoErp-UAT].dbo.Nations N ON P.IDNation = N.IDNation
LEFT JOIN [Mondo-UAT].dbo.Countries C ON N.Code1 = C.Country_Name

Migrating Data table values to Another Database Table using Script

1 Answers1