Skip to main content
Solved

Fan out to SQL Server

  • September 3, 2020
  • 5 replies
  • 50 views
R
Forum|alt.badge.img+1

Hello,

 

Is there a way to 'fanout' when writing to an SQL Database? The image below shows a simplified example of what I want to achieve , I have a number of target SQL Server tables and their table names relate to the values in Col2 (I actually have dozens of target tables to write to).

 

I'm guessing I could achieve this by writing to a format that is 'fanoutable' (e.g. Excel), then re-importing and then writing to SQL Server, but I wondered if any one knew of a more eloquent way of achieving this?

 

Example

Thanks,

 

RB

Best answer by david_r

Sure, in the output feature type parameters, set the table name to the attribute containing the table name, e.g.:

sql fanout

 As long as all the target tables have the same schema (as they do in your example), then that's all.

View original
Did this help you find an answer to your question?

5 replies

david_r
Celebrity
  • david_rCelebrity
  • Best Answer
  • September 3, 2020

Sure, in the output feature type parameters, set the table name to the attribute containing the table name, e.g.:

sql fanout

 As long as all the target tables have the same schema (as they do in your example), then that's all.

R
Forum|alt.badge.img+1
  • robinb
  • Author
  • September 3, 2020

Thanks David, that is exactly what I needed.

smfks911
  • smfks911
  • August 8, 2022
david_r wrote:

Sure, in the output feature type parameters, set the table name to the attribute containing the table name, e.g.:

sql fanout

 As long as all the target tables have the same schema (as they do in your example), then that's all.

Hi @david_r​ , I got a similar problem where I am trying to reproject a number of SQL table and then write back to same database. The concept is like the attached image. Problem is when I am connecting all the reader tables to the projector transformer, attributes/columns from all tables are getting merged into one, from where I cannot fan out the tables to write back into the same SQL server. Is there any other interim process?sql_reproject_concept_draw

david_r
Celebrity
  • david_rCelebrity
  • August 8, 2022
smfks911 wrote:

Hi @david_r​ , I got a similar problem where I am trying to reproject a number of SQL table and then write back to same database. The concept is like the attached image. Problem is when I am connecting all the reader tables to the projector transformer, attributes/columns from all tables are getting merged into one, from where I cannot fan out the tables to write back into the same SQL server. Is there any other interim process?sql_reproject_concept_draw

Did you look into using the feature attribute "fme_feature_type"? This should contain the table name from the MSSQL reader, which you can then either transform or use as-is on the writer.

Note that you may have to manually expose (make visible) this attribute on the reader first.

smfks911
  • smfks911
  • August 9, 2022
smfks911 wrote:

Hi @david_r​ , I got a similar problem where I am trying to reproject a number of SQL table and then write back to same database. The concept is like the attached image. Problem is when I am connecting all the reader tables to the projector transformer, attributes/columns from all tables are getting merged into one, from where I cannot fan out the tables to write back into the same SQL server. Is there any other interim process?sql_reproject_concept_draw

Hi @david_r​, thanks so much. Yes I have used fme_feature_type to expose and dynamic reader/writer. That worked. Also I needed to search and replace the schema that is in string otherwise a new prefix gets added and failed to read into the destination table. Do you have any suggestion if I would like to transform tables from different schemas? The string searcher/replacer allows only one string per transformation, right?

Reply


Helpful Members This Week

Community Stats

31,162
Posts
118,144
Replies
38,787
Members

Latest FME

Terms & ConditionsCookie settings

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
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.