# SQL Server - Replicate action

This action updates a selected table in your SQL Server instance. It takes a list datapill as input and inserts or updates them as rows.

Workato will first detect and compare the schema of the input data and SQL Server table. If they do not match, this action will create new columns in the SQL Server table. This ensures that all the data from your input will be automatically synced in SQL Server, even if there are updates to the input schema.

# Input

Input Description
Table name Select the table to insert your rows by typing the full table name. SQL Server is case insensitive.
Unique keys Match rows using values from these unique keys. If an existing row is found in the table, it will be updated. Otherwise, a row will be inserted.
Rows Use a list datapill as input.
Flatten columns If your input data is formatted as an object, use this to flatten the object into individual key-value pairs. Learn more.
Exclude columns Select columns from the list datapill to exclude in this replication. Excluded columns will not be added to the table.

# Output

Output Description
A new table was created true when the provided table name does not exist and is created in the job. false otherwise.
An existing table was altered true when the new columns were detected and added/altered in the table. false otherwise.
Number of rows upserted Number of rows inserted/updated in the table.

# When to use flatten

If you have data formatted as an object, you may not be able to access nested data directly (for example, nested within a JSON object).

Use this toggle to flatten the object and treat individual key-value pairs as unique columns. This action will only flatten 1 layer of nested data.

Here is an example from a data source.

{
  "ID": 1,
  "NAME": "John Doe",
  "HANDLES": {
    "GITHUB": "johndoe",
    "SLACK": "john"
  }
}

An unflattened row would look like this:

ID NAME HANDLES
1 John Doe {"github": "johndoe", "slack": "john"}

A flattened row will look like this:

ID NAME HANDLES.GITHUB HANDLES.SLACK
1 John Doe johndoe john

# Workato schema mapper

Each data source (API, Database, and File) has its own schema. To maintain consistency, this action maps each data type from the source to the SQL Server table schema. This mapping is done before creating/updating the SQL Server table.

Input data will be converted to SQL Server data types, based on the mappings defined below.

Workato type SQL Server type
string nvarchar(max)
integer bigint
number float(53)
boolean bit
object nvarchar(max)
date
date_time
timestamp
datetime2
Workato will use the timezone defined in the connection setup, or use the default SQL Server user account timezone.