CODEWITHSUNDEEP
sql-servert-sql
Vague
Vague

Reputation: 2280

combine tables into one table with TSQL

We have three tables, Agencies, Regions and Countries which we wish to combine into a new Countries table.

The old schema is

oldSchema.Agencies
AgentId PK,int
TaxName varchar(3)
TaxRate decimal(18,3)

oldSchema.Regions
RegionCode PK,tinyint
AgentId int

oldSchema.Countries
CountryCode PK,varchar(2)
CountryName varchar(50)
RegionCode tinyint

Our organisation no longer users agencies and regions so we want to combine the agency data into a newSchema.Countries table.

The new schema is

newSchema.Countries
CountryCode PK,varchar(2)
CountryName varchar(50)
TaxName varchar(3)
TaxRate decimal(18,3)

The following update query is incorrect in that it inserts identical data into every row

INSERT INTO newSchema.Countries (
    CountryCode
    ,CountryName
    ,TaxName
    ,TaxRate
    )
SELECT OldSchema.Countries.CountryCode
    ,OldSchema.Countries.CountryName
    ,OldSchema.Agencies.TaxName
    ,OldSchema.Agencies.TaxRate
FROM OldSchema.Agencies
INNER JOIN (
    OldSchema.Regions INNER JOIN OldSchema.Countries ON OldSchema.Regions.RegionCode = OldSchema.Countries.RegionCode
    ) ON OldSchema.Agencies.AgentId = OldSchema.Regions.AgentId
    WHERE NewSchema.Countries.CountryCode = OldSchema.Countries.CountryCode

How do we insert the TaxName and TaxRate for each agency into the new Countries table such that each country gets the correct tax as it was applied from the Agencies table?

Upvotes: 0

Views: 53

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

I am not sure about the problem you are facing but I think you have multiple regions with the same AgentId resulting in repeated data for a specific country.

If that is your problem try the below query, otherwise provide more details for helping you.

INSERT INTO newSchema.Countries
(CountryCode,CountryName,TaxName,TaxRate)
select distinct 
    c.CountryCode,
    c.CountryName,
    a.TaxName,
    a.TaxRate
from oldSchema.Agencies a
inner join oldSchema.Regions r on a.AgentId = r.AgentId
inner join oldSchema.Countries c on r.RegionCode = c.RegionCode

Also check this fiddle with sample data that I've created to demostration purposes.

Upvotes: 1

Related Questions