How change few columns to another table with key in SSIS?

Question

I have:

Collisions{PK_id, cashLoss, deaths, city, state, county}

I want:

Collisions{PK_id, cashLoss, deaths, #IdLocalization}
Localization{#LocalizationID(autoincremental), city, state, county} // but {city, state, county} must be distinct

How do it in SSIS and SQL?

E.g.

I can set id in table Localization as autoincremental, but how put this ID in Table Collisions with SSIS?

Answer 1

You can use SQL script as below:

/*******  DATA PREPARATION *****/

IF OBJECT_ID('tempdb..#Collisions') IS NOT NULL
DROP TABLE #Collisions

IF OBJECT_ID('tempdb..#Localization') IS NOT NULL
DROP TABLE #Localization

CREATE TABLE #Collisions (PK_ID INT, cashLoss INT, deaths INT, city varchar(20), state varchar(20), country varchar(20))
CREATE TABLE #Localization (LocalizationId INT IDENTITY (1, 1), city varchar(20), state varchar(20), country varchar(20))

INSERT INTO #Collisions (PK_ID, cashLoss, deaths, city, state, country)
SELECT PK_ID, cashLoss, deaths, city, state, country
FROM (VALUES
(1, 3, 6, 'CityA', 'StateA', 'CountryA' ),
(2, 56, 1, 'CityB', 'StateA', 'CountryA'),
(3, 54, 0, 'CityA', 'StateA', 'CountryA'),
(4, 3, 0, 'CityB', 'StateA', 'CountryA' ),
(5, 23, 0, 'CityA', 'StateB', 'CountryB')) AS T(PK_ID, cashLoss, deaths, city, state, country)



/*******  CREATE/UPDATE TABLES *****/

--Create Localization table with unique values
INSERT INTO #Localization (city, state, country)
SELECT DISTINCT city, state, country
FROM #Collisions

--Add new LocalizationId column to #Collisions
ALTER TABLE #Collisions ADD LocalizationId INT 

--Would be nice to add foreign key on #Collisions.LocalizationId

--Update LocalizationId
UPDATE C
    SET LocalizationId = L.LocalizationId
FROM #Collisions AS C
INNER JOIN #Localization AS L
    ON L.city = C.city
    AND L.state = C.state
    AND L.country = C.country

--Drop 3 columns
ALTER TABLE #Collisions DROP COLUMN city, state, country

--RESULT
SELECT * 
FROM #Collisions