Basic Syntax

As a minimum a template must consist of the following elements.

The entire document is case insensitive except for the data values which will appear in the merge statement as they are entered.

Comments

Comments can be used in the file by starting a line with the hash (#) character. Note - comments at the end of lines is not supported.

Table

Table must be the first keyword in each block. It expects a single parameter, the name of the table that the merge statement will target. The parser will validate that the parameter exists but not the parameter itself.

For example the following are all valid.

Table [dbo].[Test]
Table Test 
Table My Test 
Table [My Test]

Just because the name is valid in the template doesn't mean that it will be valid in the SQL statement. In the above "Table My Table" would not be accepted by SQL Server.

Errors like this will be caught when the database project is compiled or when you attempt to publish the database.

Key

The Key keyword must always follow the Table keyword.

The merge statement needs to know how to compare the data in the file to the data in the target table. Key expects at least one parameter but you can specify multiple key values if needed.

Separate each key value by a space.

The parser will check that each key value is unique and exists in the header row of the data block. The validation does not start until the header row has been entered.

Data

Data is just a placeholder keyword. There are no parameters for this keyword and adding one will result in a validation error.

This keyword tells the parser that the next line contains the header row (or column list).

Header Row

The Header Row contains a list of the columns to include in the merge statement. Each column is separated by a pipe character (|).

To include a pipe character as part of a column name, escape it (\|).

Each column name should be unique. A validation error will occur for any duplicate columns names.

Data Row

Immediately following the Header Row add the data rows. Again each item is separated by a pipe. The number of items must match the items in the header row.

After any number of Data Row items, you can repeat the headers as below.

As with the header row you can escape pipe characters to have them be treated as part of the data instead of a separator.