Solved

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

Posted on 2013-06-18
7
276 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
  • 5
  • 2
7 Comments
 
LVL 24

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 24

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

825 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