Create a data component template

A data component template tells DataStar how to turn rows in a table into a re-runnable deployment script. This guide walks through the minimum template, then adds the pieces you'll reach for most often.

Before you start

• You've created a workspace and connected it to a database.
• You know which table you want to generate components from.
• You know which column (or columns) uniquely identify a row, the natural key. Identity columns don't count; they change between environments.

The minimum template

Create a new XML file under your workspace's templates/ folder. The file name doesn't matter, but something like countries.xml helps when you come back to it.

tip

Workspace ▸ New Data Template opens a fresh editor tab pre-filled with a starter <DataComponent> scaffold and uses Save As to commit it to templates/; quicker than creating the file by hand.

<?xml version="1.0" encoding="utf-8"?>
<DataComponent Category="reference-data" Name="country" DeleteEnabled="true">
  <Table Id="1" Name="COUNTRY">
    <Column Name="COUNTRY_CODE" PrimaryKey="true"/>
  </Table>
</DataComponent>

Four things are going on here:

1. <DataComponent>. The root. Category groups the generated scripts in the UI; Name is the component filename.
2. <Table>. The table to extract from. Id="1" is a local identifier used when joining other tables in the same template.
3. <Column>. Only declare columns you want to customise. Everything else is picked up automatically.
4. PrimaryKey="true". Tells DataStar this column is how rows are matched. You need at least one PrimaryKey="true" or AlternativeKey="true" column per table.

That's enough to generate a MERGE script for every country row in the table. Save the file; DataStar picks up the change immediately.

Filtering to specific rows

Most of the time you don't want every row; you want the subset relevant to a specific region or set of codes. Add a <Filters> block:

<Table Id="1" Name="COUNTRY">
  <Column Name="COUNTRY_CODE" PrimaryKey="true"/>
  <Filters>
    <Value Column="COUNTRY_CODE" Value="GB"/>
  </Filters>
</Table>

Now the generated script only covers the row where COUNTRY_CODE = 'GB'. See Generate components in bulk for how to drive this filter from a query so you get one component per country.

Joining a child table

Reference tables often have child tables; COUNTRY_STATE hanging off COUNTRY, for example. Declare both tables and link them:

<Table Id="1" Name="COUNTRY">
  <Column Name="COUNTRY_CODE" PrimaryKey="true"/>
</Table>

<Table Id="2" Name="COUNTRY_STATE">
  <Column Name="COUNTRY_CODE" PrimaryKey="true" SortOrder="1"/>
  <Column Name="STATE_CODE" PrimaryKey="true" SortOrder="2"/>
  <JoinTable ReferenceId="1" Relationship="ManyToOne">
    <JoinColumn Column="COUNTRY_CODE" ReferenceColumn="COUNTRY_CODE"/>
  </JoinTable>
</Table>

ReferenceId="1" points back to the parent table by its Id. SortOrder on multi-column keys controls the column order in the generated MERGE.

Column behaviour cheatsheet

You'll use these attributes on <Column> most often:

Attribute	When to use it
PrimaryKey="true"	Natural key: what identifies the row
AlternativeKey="true"	Business key when the PK is a transient identity; see alternative keys
Transient="true"	Value differs across environments (e.g. an identity column)
InsertExclude="true"	Skip this column in INSERT (e.g. identity columns)
UpdateExclude="true"	Skip this column in UPDATE (e.g. CREATED_DATE)
UpdateTrigger="false"	This column alone doesn't count as a diff (e.g. UPDATED_BY)
Value="N"	Override the extracted value with a constant
<Lookup>...</Lookup>	Resolve a foreign key by business key; see lookups

Root element options

Attributes on <DataComponent> you'll reach for:

Attribute	Effect
DeleteEnabled="false"	Generate INSERT/UPDATE only; don't delete rows missing from source
Schema="..."	Default schema for every table unless overridden on <Table>
Version="1"	SQL Server only. Keeps the older delete-inside-the-MERGE script; version 2 is the default since 3.1. See SQL Server: Version 2 templates below.
OutputFormat="json"	Store the component as a data document of rows rather than a .sql script, and generate the script when it deploys. See Store data as documents.
DeployMode="changeset"	Deploy only the rows one work item changed, for a table several people work in at once. See Deploy only your rows.
ExactComparison="true"	SQL Server only. Deploy a change of case, accent or width on its own, which a case-insensitive database otherwise treats as no change. See SQL Server: case-only changes below.
ExcludeDuplicates="true"	Silently exclude duplicate components instead of flagging them as errors. See handling duplicates.

SQL Server: case-only changes

A SQL Server data script updates a row only when its values differ from the target's, and it compares them under the database's collation. On a case-insensitive database, which is the SQL Server default, abc and ABC are the same value, so a change of case on its own is never deployed. The same goes for accents on an accent-insensitive database.

ExactComparison="true" on the root element compares the values exactly instead, so those changes are deployed:

• A row whose only difference is case, accents or width is updated.
• The alternate key columns rows are matched on are updated too, so renaming a key from ALERIANINDEX to AlerianIndex renames the row in place. It keeps its generated id and its children.
• Rows are still matched, and rows to delete still found, under the database's collation.

It is off by default, and a template without it generates exactly the script it did before. Turning it on changes the script of every component the template generates, and the first deployment afterwards updates any target rows that differ from the source only in case. Oracle always compares exactly, so the attribute has no effect there; on Oracle a key renamed only in case is a different row, deployed as a delete and an insert, which matters for changesets.

SQL Server: Version 2 templates

Applies to SQL Server only. The Version attribute on a data component's root element changes how DataStar generates deletes. Since 3.1 a template that does not set it is version 2; set Version="1" to keep the older script.

Behaviour	v1 (Version="1")	v2 (the default)
Where deletes live	Inside the MERGE via WHEN NOT MATCHED BY SOURCE THEN DELETE	Moved to the end of the script, one DELETE per table, run in reverse table order
Foreign-key handling	Prone to constraint violations when deletes cross tables; typically worked around with constraints-disable.sql / constraints-enable.sql pre- and post-scripts that toggle NOCHECK CONSTRAINT ALL	Reverse-order deletes naturally satisfy FK dependencies, so the disable / enable scripts are usually not needed
Reversal order	Original order by default	Pass --inverse-reversal to DataStar.Tools so reversals also run in reverse table order

For new SQL Server components, v2 is the simpler choice, which is why it is now the default. For a workspace written against v1, the change of default means a template that never set Version now generates v2 scripts the next time its components are extracted; that may interact with pre-existing constraint-toggle scripts, so review those, or set Version="1" on the templates you are not ready to move. A parent's delete also cascades differently: v1 cascades to a joined child unless the child sets DeleteCascade="false", v2 only where the child sets DeleteCascade="true".

The setting is SQL Server only; it has no effect on Oracle templates. Oracle has always generated scripts the way v2 does for SQL Server: the MERGE handles inserts and updates, and deletes run separately at the end of the script in reverse table order. For the same reason, DataStar.Tools runs Oracle reversals in reverse order by default, while on SQL Server you pass --inverse-reversal to opt into that behaviour.

What to do next

• Rows keyed by identity? Add alternative keys so deployments port across environments.
• Rows that reference other rows by ID? Add lookups so those IDs get resolved at deploy time.
• One template, many components? See Generate components in bulk.
• Data living in a sibling database on the same server? See Target a different database.
• Want the rows in version control rather than a script, so a pull request shows what changed? See Store data as documents, and for a table several people change at once, changesets.
• Full schema reference: the DataComponent.xsd shipped with DataStar covers every element and attribute.