Converting to SQL Server: Write to an Existing Table

Introduction

Often, a new FME workspace creates database tables that are a duplicate of the incoming data. Sometimes a user will wish to write to a database table that already exists.

When the table already exists, the workspace should reflect the table schema, not the source. This can be achieved by importing the schema of that table from the database. While adding a database writer, the import option allows a user to use the schema of a database table that has been previously created.
 

Video

Step-by-Step Instructions

1. Add the Reader

Start a new workspace in FME Workbench. Add an Esri Shapefile writer and browse to the BikePaths_L.shp dataset. Click OK to add the reader.


Single-click on the reader feature type to open the popup menu, then click on View Source Data to open the data in Visual Preview. Select File > Open Dataset. Set the Format to Esri Shapefile and the Dataset to BikePaths_L.shp.

The Data Inspector will display the shapefile. Select a feature on the display, the Feature Information window will populate with the feature's properties. Here, we can see that the shapefile coordinate system is UTM83-10.

2a. Add the Writer

Add a Writer to the workspace and set Microsoft SQL Server Spatial as the format. The dataset should be set to the SQL Training Database connection created in Viewing and Inspecting SQL Server data.

Open the Parameters dialog for the Reader to check the Spatial Type is set to Geometry since we know the shapefile is in UTM coordinates.

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: Geometry

Set the Table Definition to Import from Dataset. This will import a schema from an existing table for use in the Writer. Click OK.
 

2b. Import Feature Types

FME will ask where the Feature Types are to be imported from. In this case they are the same location as the Writer dataset. This may not always be the case. Keep the Format as Microsoft SQL Server Spatial and the Dataset as the named connection (SQL Training Database).

Click on the Parameters button and select BikePaths from the Table List. If you clicked OK instead of Parameters, FME will prompt you to verify the parameters are correct.

Leave all other fields in this dialog as is.
 

3. Set Feature Operation and Table Handling on the Feature Writer

Open the Feature Type Properties for the feature writer. Under the Parameters tab, verify that the Feature Operation parameter is set to 'Insert', and change the Table Handling to 'Use Existing'. Leave the Spatial Type as 'Inherit From Writer'.

4. Run the Workspace

Connect the Reader to the Writer and run the workspace.

If the workspace is run more than once, the new data will be added repeatedly!

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.