Data Syntax

Quick Reference

Syntax:
#schema.table
| columnname[options]	| columnname[options]
----------------------------------------------------------------------------------
dataA			| dataA1
^			| dataA2
^			| dataA3

dataB			| dataB1
^			| dataB2	
^			| dataB3

  • Table is specified by starting the line with a hash character (#) and then specifying the schema and table names in the following format: schema.table
  • The second line is the column row, and should start with a pipe character (|) and should list the columns whose values are being specified, separated by pipe characters
  • Lines starting with a dash character (-) are ignore. A line full of dashes is convenient to use as the boundary for the column header
  • Remaining lines specify data, and should also be separated by pipe characters
  • Using tabs, it is possible and recommended to line up the values and pipe separators to give the appearance of a table

Column Options
Token Description
! Unique Key - Values specified in this column should be unique. If an existing value is found, the remaining columns will be updated to match the values specified. Multiple columns may be marked with ! to match multiple values
columnname>schema.table.lookupcolumn=valuecolumn Lookup - Useful for Foreign Key columns, allows textual data to be specified instead of a numeric identifier, which has the effect of making the data markup much more readable


Data Options
Token Description
<RelativeFilePath.txt Import external file - Specify a path relative to the source file to load larger blocks of text or binary content.
^ Reuses the value from the previous row

Ensuring Unique Values ("Upsert"/"Define" functionality)*

"Upsert" or "Define" functionality can be accomplished using the ! token on one or more columns.

Example:
Using the ! token on the name column will ensure that each of the values listed will only be added to the table once. In this example, categories named Yellow Junk Food and Brown Junk Food will only be added if they don't already exist in the table.
#dbo.categories
| name!
---------------------------------------------------------------------------------
Yellow Junk Food
Brown Junk Food


Example:
It can also be used to ensure certain rows are given a specific value. In this example, Yellow Junk Food and Brown Junk Food will only be added if the don't already exist. If they do exist, the generated script will ensure that they have the correct ids. Note that this will only work if the id column does not have an IDENTITY specification.
#dbo.categories
id	| name!
---------------------------------------------------------------------------------
1	| Yellow Junk Food
2	| Brown Junk Food

Foreign Key Lookups

Using the > token allows values to be retrieved from another table. This is beneficial since it allows foreign key and other mapping tables to be much more easily readable in the markup, and also makes it possible to reference rows that are inserted earlier in the script, even if their IDs won't be known until runtime.

Example:
In this example, values are first specified for the categories table. Then, the names of the categories (rather than the IDs, which would not be known) are used to force the script to look up the id of the correct category before adding each product row.
#dbo.categories
| name!
---------------------------------------------------------------------------------
Yellow Junk Food
Brown Junk Food

#dbo.products
| name!		| category_id>dbo.categories.name=id
---------------------------------------------------------------------------------
Twinkie		| Yellow Junk Food
Ding Dong	| Brown Junk Food

Importing Files

  • Using the < token, it is possible to create scripts that insert large text values, images or other binary files.
  • .HTM, .SQL, .TXT and .XML files are loaded as Unicode text; all other files are loaded as binary values.

Example:
#dbo.products
| name!				| description			| image
---------------------------------------------------------------------------------
Twinkie				| <TwinkieDescription.txt	| <TwinkieImage.png
Ding Dong			| <DingDongDescription.txt	| <DingDongImage.png

Last edited Aug 13, 2010 at 7:10 PM by DanielSchaffer, version 11

Comments

No comments yet.