Solved

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

Posted on 2013-06-18
7
282 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

635 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