Solved

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

Posted on 2013-06-18
7
280 Views
Last Modified: 2016-02-11
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
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39258508
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
 

Author Comment

by:zolf
ID: 39258528
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
 

Author Comment

by:zolf
ID: 39258538
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39258552
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
 

Author Closing Comment

by:zolf
ID: 39258567
perfect....cheers mate
0
 

Author Comment

by:zolf
ID: 39258578
can you please help me to split the district and city too,since you know my initial problem.
0
 

Author Comment

by:zolf
ID: 39258585
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question