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 
	{ 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

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 'datetime dateFound=#utcnow(s)' IGNORE_IF_EXISTS;

		-- Adding another columns, as a string; because we are formatting with 
		-- inner quotes, we can surround the expression with parenthesis 

		ADD COLUMN (dateFoundStr='#utcnow(s)') 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=#utcnow()' IGNORE_IF_EXISTS;