Converting to SQL Server: Create, Drop or Truncate a Table

Introduction

Writing data into a new table in a database is not much more complex than writing out to a new file. There are several Feature Type writer parameters that enable users to control database writing.

Feature Type Parameters

Spatial Type

Microsoft SQL Server Spatial offers the ability to set a Spatial Type. Geometry supports planar or Cartesian coordinates, such as UTM, while Geography supports ellipsoidal data, such as lat/long. More information on Spatial Type and other SQL Server Spatial Writer parameters can be found in the MSSQL Spatial Writer documentation.

Feature Operation

Updates and deletions to a database can be primarily controlled through the Writer parameter: Feature Operation. This parameter instructs FME on which action to take on the database. Its three values are: Insert, Update, and Delete.

• Insert results in the records added to the database. This is used to add data to a new or existing table in the database. Rows are appended to the table.
• Update means that records are not being inserted or deleted, but instead replaced. Each FME feature written to a database in Update mode replaces an existing database record.
• Delete will remove records from a database. Each FME feature written to a database in Delete mode will result in a database record being deleted.

Table Handling

Several Table Handling parameters exist to help create or update existing database tables:

• Use Existing: Uses the existing table; if the table does not exist, the translation will fail.
• Create if Needed: Writes to an existing table or creates a table if it does not exist.
• Drop and Create: Drops the destination table and then recreates it prior to loading the data.
• Truncate Existing: Deletes all rows from the existing table, leaving the table definition intact; if the table does not exist, the translation will fail.

Bulk Insert

Additionally, SQL Server writers have the option to set Bulk Insert (from the Navigator pane). If 'Yes' is selected, the insertion mode will be changed to batch from feature-by-feature, resulting in a faster insertion speed. However, there is reduced granularity in errors. Should a large translation fail, there will be more rows to insert when the batch fails, as opposed to inserting features one by one.

Video

This video was recorded using FME 2016.1. The interface may differ, but the concepts remain the same.

Step-by-Step Instructions

This demonstration will load the public art data in the CSV file into a new table in the database.

1. Add the CSV Reader

As of FME 2025.2, the Coordinate System parameter is now configured within the Parameters dialog of each reader/writer format. For more information, including details about the change and affected transformers, please see Coordinate System Parameter Location Change.

• In a blank workspace, click the Add Reader icon and add a reader with the following parameters:

  Format: CSV (Comma Separated Value)

  Dataset: /4216-downtown.csv

  

  Click the Parameters button to set the coordinate system:

  Attribute Definition: Manual

  Name: Longitude

  Type: x_coordinate

  Name: Latitude

  Type: y_coordinate

  Create Point Geometry from Attributes: Enabled

  X Coordinate Attribute: Longitude

  Y Coordinate Attribute: Latitude

  Coordinate System: LL84

  

  Click OK to add the reader to the workspace.

• In a blank workspace, click the Add Reader icon and add a reader with the following parameters:

  Format: CSV (Comma Separated Value)

  Dataset: /4216-downtown.csv

  Coordinate System: LL84

  

  In the Parameters dialog, confirm that the Longitude and Latitude attributes are set to x_coordinate and y_coordinate, respectively. If they are not, change the Attribute Definition to Manual, then manually set.

  

  Click OK to add the reader to the workspace.

2a. Add the SQL Server Writer

From the Writers menu, Add Writer.

Set the format to Microsoft SQL Server Spatial, and the dataset to the SQL Training Database connection established in Viewing and Inspecting SQL Server data.

Starting in FME 2025.2, many formats that support both spatial and non-spatial data have been combined into a single reader or writer. For an up-to-date list of the formats this change has been applied to, please see Combined Spatial and Non-Spatial Readers and Writers.

Open the Parameters dialog for the Reader and set the Spatial Type to Geography. Clear the contents of the Spatial Column.

If you do not have this named database connection, select the option to Embed Connection Parameters, and after selecting Parameters, enter the parameters manually:

• Server: sql.fmetraining.ca
• Database: fmedata
• Authentication: SQL Server Authentication
• Username: fmedata
• Password: SQLtraining2016
• Spatial Type: Geography

Set the Table Definition to Copy From Reader, so that the attributes from our source are copied into the SQL Server database table.

2b. Format the Writer

After adding the Writer to the canvas, open the Feature Type Properties. In the General tab, change the Table Name to Downtown_PublicArt, and enter the Table Qualifier as fmedata2016.

The Table Qualifier is the same as the Schema for the Table Listing in the previous Reader parameters. It is specified per database table, as one could write to multiple schemas using the same database writer.

In the Format Attributes tab, check the Table Settings parameters. When creating a new table in SQL Server and adding data to it, the Feature Operation should be set to 'Insert', and the Table Handling should be set to 'Create If Needed'. The Spatial Type can be left as Inherit From Writer.

Click OK to close the parameter window, then connect the Reader to the Writer.

3. Run the Workspace

The SQL Server database will now have the Downtown_PublicArt table. Right-click on the Writer and select Inspect to see the geometry and attributes of the table displayed in FME Data Inspector.

Additional Table Handling Parameter Options

To replace the entire contents of a table, use the Table Handling parameters "Drop and Create" and "Truncate Existing".

"Truncate Existing" is used when the table needs to be emptied of existing data but does not otherwise need an update to its schema or table structure. "Drop and Create" is used when the table needs to be emptied and an update is made to the database schema. For example, this is used when you wish to update a table with new content and require a new column to be added to the table.

When using either of these, set the Feature Operation parameter to Insert. Setting it as Update or Delete will be of no use once the existing table has been emptied.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.