Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-18
7
Medium Priority
?
285 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 25

Accepted Solution

by:
chaau earned 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

721 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