I’m working on a project where I need to replicate the table structure from an Oracle to SQL Server but without transferring any data. Essentially, I want to:
Read tables from Oracle, recreate the same tables in SQL Server and write only the schema (field names, data types), not the records
Is there a best practice to force FME to write only the schema?
Are there limitations I should be aware of (e.g. no support for constraints, indexes)?
You can utilise dynamic writing in FME to achieve this.
FME can dynamically determine the schema based on the source data or a specified schema source. This is particularly useful when dealing with data where the schema might change frequently, or when you need to write to multiple tables with different schemas.
Dynamic Writers can be a bit tricky, especially when you're using the Table Qualifier option. The easiest way, and this also gives the most flexibility, is to leave the Table Qualifier blank and just prefix the fme_feature_type_name and fme_feature_type attributes with the right Table Qualifier
basic with traditional writer https://support.safe.com/hc/en-us/articles/25407735685773-Dynamic-Workflows-Components
using a feature writer workflow transformer - this is my preferred method:
RE: schema only - The latest versions of FME support writing schema only, older versions you need to create the tables with some features, then you can truncate thereafter