Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Compare strings in 2 table and asisgn its primary key to the second table

Hello there,

I have two tables which has name common in both the tables. now I want to compare the names and assign the pk of table1 to table2 as FK. one thing to mention is that the name cole is a Nvarchar i.e. it is UTF-8 character.
by the way can I do this using SSIS

cheers
ZOlf
0
zolf
Asked:
zolf
  • 5
  • 2
1 Solution
 
chaauCommented:
Yes, you can easily do this with SQL. In SSIS you will have to open the Query Analiser and construct and run a few SQL queries. Now, it would be great if you have specified the table names and the column names. That way we could write you the queries you can execute directly against your tables. Can you post this information?
0
 
zolfAuthor Commented:
I am trying to get the id from province table into the country table


CREATE TABLE
    country
    (
        id BIGINT NOT NULL IDENTITY,
        Province NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        County NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        District NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        City NVARCHAR(255) COLLATE Latin1_General_CI_AS
    );

CREATE TABLE
    province
    (
        id INT NOT NULL IDENTITY,
        Province NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        Capital NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        ProvinceCode INT,
        PRIMARY KEY (id)
    );

Open in new window

0
 
zolfAuthor Commented:
by the way I am trying to using SSIS using Data Flow Task and Lookup,but it is not working. anyway if we can get it with sql fine with me.just bear in mind my data in UTF-8 character not english
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
chaauCommented:
Cool.

Please note the following. Before you proceed with the below code please make sure you have backed up you database. I am not responsible for any data loss/damage. If possible, apply the changes against your staging/dev/test database

First, you will have to add a column to you country table that is going to be called ProvinceID. That will be a foreign key.
Use this query:
ALTER TABLE country ADD COLUMN ProvinceID INT;

Open in new window


This step is optional, but is nice to have. It is possible that you have some provinces recorded in your country table that are not in province table. You can insert them into province table for completeness:
INSERT INTO province (province)
SELECT province FROM country
WHERE province NOT IN (SELECT province FROM province);

Open in new window


Now, you will have to populate the ProvinceID column.
UPDATE country
SET country.ProvinceID = province.ID
FROM country INNER JOIN province ON country.province = province.province;

Open in new window


Now, you need to add the foreign key
ALTER TABLE country
ADD CONSTRAINT FK_country_province FOREIGN KEY (ProvinceID)
    REFERENCES province(ID);

Open in new window


At this stage we have completed adding the relationship for the tables.

Optionally, you can drop now redundant column province from the table country:
ALTER TABLE country DROP COLUMN province

Open in new window


I hope everything is ok here. I have not tested the scripts, there could be typos. Please let me know if you come up with any errors
0
 
zolfAuthor Commented:
perfect....cheers mate
0
 
zolfAuthor Commented:
can you please help me to split the district and city too,since you know my initial problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now