Beginning with stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Beginning with stored procedure

Author

Topic

nbourre
Starting Member

14 Posts

Posted - 2006-12-29 : 17:08:59

Hi,

I am working on a non-relational Filemaker DB (FM) (A mess!) and I'm developping a new relationnal DB in SQL Server so I can transfert the whole Filemaker DB on SQL Server. I know how to develop a database, but I'm unfamiliar with stored procedure.

Here is one of my problem. I imported a table from FM which was not normalized. I created some normalized tables, now I want to fill these tables with the unormalized one. How can I do this with stored procedure?

By Example:

An non-normalized person table has a province field which is a text field.

I normalized the table by creating a Province table with two fields pkProvinceID (Identification) and ProvinceName.

I created a relation between the Person and Province tables. Joined by pkProvinceID and fkProvinceID

The question is how do I fill the fkProvinceID in the person table with the right province?

I presume that the algorithm should be like this:
UPDATE Person
SET fkProvinceID = (
SELECT pkProvinceID
FROM Province
WHERE Person.Province = Province.ProvinceName
)
WHERE fkProvinceID = NULL

Any help would be great!

Thank you

Nick
Beginning with something is a good start!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-29 : 17:28:07

UPDATE pe
SET ProvinceID = pr.ProvinceID
FROM Person pe
INNER JOIN Province pr
ON pe.ProvinceID = pr.ProvinceID

You should reconsider adding pk and fk to your column names. It is apparent which one is which in a query when you use aliases.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources