ADD COLUMN

Adds a column to the current pipeline data and sets its data type and default value as specified. If EXPRESSION is not provided, the value of the variable will be set to NULL. If the column name already exists in the pipeline data set, this component will throw an error unless the IGNORE_IF_EXISTS flag is set.

This component supports a similar syntax to APPLY SCHEMA for a simpler specification. The column name can contain the full field definition without the need to use the EXPRESSION or DATA_TYPE parameters. For example, the two statements below are equivalent, but the second option supports the fully APPLY SCHEMA specification providing additional options including applying set-based or row-based functions:

ADD COLUMN 'guid' WITH EXPRESSION '#rndguid()' DATA_TYPE 'string'

ADD COLUMN 'nvarchar(36) [guid] = @rndguid()'

Adds a column to the current data set in a pipeline and sets a default value either as a calculation or the outcome of a DataZen function operation.

ADD COLUMN 
	'...'
{ WITH 
	{ EXPRESSION '...' }
	{ DATA_TYPE < 'string' | 'int' | 'long' | 'datetime' | 'guid' | 'char' | 'bool' | 'float' | 'double' | 'byte' | 'byte[]' > }
	{ MAX_LEN <n> }
	{ IGNORE_IF_EXISTS }
	{ CONTINUE_ON_ERROR }
}
;

COLUMN	The name of the column to add to the data set; if the column name already exists, this component throws an error unless the IgnoreIfExists option is set
CONTINUE_ON_ERRORS	Continues processing even if errors are detected
IGNORE_IF_EXISTS	Bypasses this component if the column name already exists
EXPRESSION	The expression used to calculate the value of this column, or the DataZen function to execute. The expression can be a simple SQL clause (ex: [field1] + [field2]), or a DataZen function (ex: #rndguid())
DATA_TYPE	The data type to use for this column (default: string). Supported types: string, int, long, datetime, guid, char, bool, float, double, byte, byte[]
MAX_LEN	The max length to use for this column when the data type is a string (-1: unlimited)

Example 1

-- This HTTP request uses the Rss Feed connection and calls the /econi.xml relative URL
-- The APPLY operation transforms the HTTP response body using an XPath command that 
-- turns the payload into rows and columns automatically
-- The component is added "inline" of the HTTP operation

SELECT * FROM HTTP [Rss Feed] (GET /econi.xml) APPLY TX (//item)

	-- This pipeline executes as part of the HTTP request
	-- If the HTTP request was made over multiple pages, this component would 
	-- execute for each page of data; since we are not using paging, 
	-- this will excute only once.

	APPLY PIPELINE (
		
		-- Now, add a column with the current time un UTC and 
		-- ignore this operation if the columns already exists
		-- The data type will be a datetime 

		ADD COLUMN 'dateFound' WITH EXPRESSION '#utcnow()' DATA_TYPE 'datetime' IGNORE_IF_EXISTS;

	)
;

Example 2

-- This HTTP request uses the Rss Feed connection and calls the /econi.xml relative URL
-- The APPLY operation transforms the HTTP response body using an XPath command that 
-- turns the payload into rows and columns automatically
-- The component is executed after the HTTP execution is completed

SELECT * FROM HTTP [Rss Feed] (GET /econi.xml) APPLY TX (//item);

-- Now, add a column with the current time un UTC and 
-- ignore this operation if the columns already exists
-- The data type will be a string

ADD COLUMN 'dateFound' WITH EXPRESSION '#utcnow()' IGNORE_IF_EXISTS;