Migrating table values to another Database table in SQL Server

relaxingmusicym
 
on Sep 27, 2022 07:54 AM
314 Views

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

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 27, 2022 07:55 AM

Hi relaxingmusic...,

Will you please share some sample data for better understanding with the expected output.

relaxingmusicym
 
on Sep 27, 2022 09:43 PM
dharmendr says:

Hi relaxingmusic...,

Will you please share some sample data for better understanding with the expected output.

 This is the old database data

This is the old database data, Table Province and Country Joined. As I mentioned in my question 

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 database Country table

I have migrated the old database country list to the new database country table. 

But you see the Ids are different in the tables. So the old database province table contained the old database countryId(IDNation) and the Province Name.

So I want to match the country name and get the new country Id and the related provinces to the new temporary table to create an insert query to the new database Province table.

 

PrinceG
 
on Sep 28, 2022 04:26 AM

Hi relaxingmusic,

Please refer below sample Query.

SQL

CREATE TABLE ##Provinces
(
	Name VARCHAR(20),
	Code1 VARCHAR(20),
	IDNation INT
)

CREATE TABLE ##Nations
(
	IDNation INT,
	Code1 VARCHAR(20)
)

INSERT INTO ##Provinces VALUES
			('Torino','TO',249),
			('Vercelli','VC',249),
			('Novara','NO',249),
			('Cuneo','CN',249),
			('Asti','AT',249),
			('Alessandria','AL',249),
			('Biella','BI',249),
			('Verbania','VB',249)

INSERT INTO ##Nations VALUES (249,'Italy')

CREATE TABLE ##Country
(
	ID INT,
	Country_Name VARCHAR(20)
)

INSERT INTO ##Country VALUES (97,'Isle of Man'),(98,'Israel'),(99,'Italy')

SELECT  P.Name,
        P.Code1,
        P.IDNation,
        N.IDNation,
        N.Code1,
		C.ID
FROM ##Provinces P
LEFT JOIN ##Nations N ON P.IDNation = N.IDNation
INNER JOIN ##Country C ON C.Country_Name = N.Code1

Screenshot