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 a number of Feature Type writer parameters that allow 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 informs FME which action to implement onto the database. Its three values are: Insert, Update, and Delete.

• Insert results in records added to the database. This is used to add data to a new or existing table in the database. Rows are appended onto 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 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 re-creates the table prior to loading the data.
• Truncate Existing: Deletes all rows from the existing table but leaves the table definition, 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 greater 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 feature-by-feature insertion.
 

Video

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

Start FME Workbench. On the Start Page, select New Workspace to create a blank canvas.

Drag the Downtown.csv file onto the canvas from Windows Explorer, generating a CSV reader. In the Add Reader dialog, if not correct, set CSV for the source format and Downtown.csv for the dataset.

Click the Parameters button on the Add Reader window. In the Attributes section, change the attribute definition to manual and set the columns Longitude and Latitude to type x_coord and y_coord respectively. This informs the reader to create points from the CSV data as it reads the file. Click OK to close the parameters window.

In the Add Reader window, set the coordinate system to LL84. Each point will be tagged with this coordinate system as it's read. 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.



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 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. As we are creating a new table in SQL Server and adding data into it, the Feature Operation should be left as Insert, and Table Handling as 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.