CODEWITHSUNDEEP
sqlsql-servert-sql
Gokay
Gokay

Reputation: 3

Insert from multiple related tables during database migrating

I'm migrating Oracle DB to SQL Server via linked servers. Here is my question;

I have table on Oracle like this:

OldPersonelTable

PERSONID SSN  COUNTRY   CITY
-------------------------------
   1     123  RUSSIA    MOSCOW
   2     124  TURKEY    ISTANBUL
   3     125  TURKEY    ANKARA

And on my new SQL Server database I have tables like this:

CountryTable

COUNTRYID COUNTRYNAME
---------------------    
1         RUSSIA
2         TURKEY

CITYTABLE

CITYID COUNTRYID CITYNAME
-------------------------    
1          1      MOSCOW
2          2      ISTANBUL
3          2      ANKARA

NewPersonelTable

PERSONID SSN  COUNTRY   CITY
1        123   1          1
2        124   2          2
3        125   2          3

Yes that's all. I just wanna write a insert query selects from OldDatabase and insert into new personel table. Old db does not have any city and country id table. Only chance to join is as you see city and country names.

edit:Citynames are not unique.

Any help would be appreciated.

Upvotes: 0

Views: 110

Answers (2)

Atheer Mostafa
Atheer Mostafa

Reputation: 735

Doing this migration task required to be in two steps: 1- Copy the main data 2- Copy the relational data

Be sure first that the new tables primary keys are IDENTITY. As follows: 1) Copy Data

INSERT INTO CountryTable(COUNTRYNAME)
SELECT DISTINCT COUNTRY FROM OraLink...OldPersonelTable

INSERT INTO CityTable(CITYNAME)
SELECT DISTINCT CITY FROM OraLink...OldPersonelTable

INSERT INTO NewPersonelTable(SSN)
SELECT DISTINCT SSN FROM OraLink...OldPersonelTable

2) Copy Relations

UPDATE CityTable SET COUNTRYID=CountryTable.COUNTRYID 
FROM OraLink...OldPersonelTable O INNER JOIN CountryTable
ON O.Country=CountryTable.COUNTRYNAME
WHERE CITYNAME=City

UPDATE NewPersonelTable SET COUNTRY=CountryTable.COUNTRYID, CITY=CityTable.CITYID
FROM OraLink...OldPersonelTable O INNER JOIN CountryTable
ON O.Country=CountryTable.COUNTRYNAME
INNER JOIN CityTable ON O.CITY=CityTable.CITYNAME
WHERE NewPersonelTable.SSN=O.SSN

Upvotes: 0

Steve Mayne
Steve Mayne

Reputation: 22858

Assuming CITYTABLE and CountryTable are already populated, you can populate NewPersonelTable like this:

If PERSONID is the primary key of NewPersonelTable, then you'll probably need to do:

SET IDENTITY_INSERT ON

then run:

INSERT INTO NewPersonelTable ( PERSONID, SSN, COUNTRY, CITY )
SELECT T.PERSONID, T.SSN, C.COUNTRYID, CITY.CITYID
FROM OldPersonelTable T JOIN CountryTable C ON
  T.COUNTRY = C.COUNTRYNAME JOIN CITYTABLE CITY ON
  T.CITY = CITY.CITYNAME AND
  C.COUNTRYID = CITY.COUNTRYID

Upvotes: 1

Related Questions