+24Similar to FileGDB (https://knowledge.safe.com/questions/1652/writing-relationships-in-esri-file-geodatabase.html)
Is it possible to easily write to a relation-table on MS SQL? And other databases? I see the db_relationship does not exist in those formats
Best answer by takashi
Hi @sigtill, I sometimes add a foreign key constraint to tables with a SQL statement written in the 'SQL Statement to Execute After Translation' parameter for the database writer. e.g.
alter table table1
add constraint fk_table1_table2 foreign key (id)
references table2 (id);
Hi @sigtill, I sometimes add a foreign key constraint to tables with a SQL statement written in the 'SQL Statement to Execute After Translation' parameter for the database writer. e.g.
alter table table1
add constraint fk_table1_table2 foreign key (id)
references table2 (id);Hi @sigtill, I sometimes add a foreign key constraint to tables with a SQL statement written in the 'SQL Statement to Execute After Translation' parameter for the database writer. e.g.
alter table table1
add constraint fk_table1_table2 foreign key (id)
references table2 (id);
Relationships in traditional SQL databases (non-SDE) work a bit differently than in e.g. a FileGDB, there is no such thing as a relationship-table. The relationships between two tables are defined with foreign key constraints only, the relationships aren't materialized in separate entities.
If we use Takashi's example, it means that you'll first have to write the data in "table2" before you can write the data to "table1", since it references "table2". In FME this often means having to use two writers, one for "table1" and one for "table2", then making sure that "table2" is written first by having it at the top of the Navigator.
If you think this is a hassle, consider voting for the idea I posted earlier today :-)
Let me know if I've misunderstood your question.
FME_SQL_DELIMITER ;
create table table1 (
id integer,
something varchar(100)
constraint pk_table1 primary key (id)
);
create table table2 (
id integer,
something varchar(100)
constraint pk_table2 primary key (id),
constraint fk_table2_table1 foreign key(id) references table1 (id)
);
+44Relationships in traditional SQL databases (non-SDE) work a bit differently than in e.g. a FileGDB, there is no such thing as a relationship-table. The relationships between two tables are defined with foreign key constraints only, the relationships aren't materialized in separate entities.
If we use Takashi's example, it means that you'll first have to write the data in "table2" before you can write the data to "table1", since it references "table2". In FME this often means having to use two writers, one for "table1" and one for "table2", then making sure that "table2" is written first by having it at the top of the Navigator.
If you think this is a hassle, consider voting for the idea I posted earlier today :-)
Let me know if I've misunderstood your question.
+24Thanks for the answers! I had an option similar to Takashis, however I struggled with the Featurewriter finishing before starting to use the writer. Using the Creator Featuremerger solves that issue, thanks @takashi
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
