Skip to main content
Question

How to insert into multiple table and manage key relationship?

  • 17 February 2017
  • 6 replies
  • 114 views

Hello,

I have a non spatial data set in Excel format.For each row i have to split the data into two tables.First I need to enter some of the attributes in the "Property" table and then into "Contact" table.

"Contact" table contains the primary key of Property in "PropertyId" column as Foreign key.To insert the data successfully I need to have the id of the row inserted in Property Table and the store it in the Contact table.

Is there a way to achieve this functionality with FME Desktop?

Thank in advance.

david_r
Evangelist
  • February 17, 2017

Have a look at this article: https://knowledge.safe.com/articles/201/write-feature-types-in-a-specific-order.html

Basically you create separate writers for each table and order them according to your foreign key constraints.


erik_jan
Contributor
Forum|alt.badge.img+17
  • Contributor
  • February 17, 2017

The FeatureHolder transformer can be useful too.

But be careful with that one: it stores data in memory and can slow down your process flow quite a bit.

Last option: use multiple workspaces and chain them together using the WorkspaceRunner transformer.

I prefer the last one as it makes the workflow more flexible.


itay
Contributor
Forum|alt.badge.img+16
  • Contributor
  • February 17, 2017

Hi @johnbutton,

Yes the easiest way is to first drop the constraint write the data and add them again.

If that is not an option consider writing to the property table first with the FeatureWriter and use the FeatureWriter output feature to read the PropertyID.

Then Join it to the Contact features and write them via a normal writer.

Hope this helps.


david_r
Evangelist
  • February 17, 2017
itay wrote:

Hi @johnbutton,

Yes the easiest way is to first drop the constraint write the data and add them again.

If that is not an option consider writing to the property table first with the FeatureWriter and use the FeatureWriter output feature to read the PropertyID.

Then Join it to the Contact features and write them via a normal writer.

Hope this helps.

Be careful dropping constraints though, if you have a lot of previous data in your table you can in some instances risk that your database will create a lot of hassle when re-creating the constraints after.

 

I think using the FeatureWriter is a great idea.

itay
Contributor
Forum|alt.badge.img+16
  • Contributor
  • February 17, 2017
david_r wrote:
Be careful dropping constraints though, if you have a lot of previous data in your table you can in some instances risk that your database will create a lot of hassle when re-creating the constraints after.

 

I think using the FeatureWriter is a great idea.

 

Agreed when it's a complex situation, it's better to work with the confines of the database.

Forum|alt.badge.img+7

Hi @johnbutton

I understand you want to know how to generate the id as primary key for the property and afterwards use this value as a foreign key for the contact info.

To implement this functionality, you can create the unique identifier (with a Counter) right before making a distinction between the contact- and property info. Doing so will make sure that both tables have the attribute 'id'. For the contact table, you just have to rename this attribute to 'propertyId' and make sure that the writer writing to the table containing the properties is executed first by putting it on top in the Navigator.

Conceptual schema:


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings