Skip to main content

Celonis Product Documentation

MSSQL SQL

Celonis Execution Management System (EMS) offers several predefined statements and functions which can be used in your transformations. Additionally, there are some differences to SQL Server Studio.

Executing queries in batches

Celonis EMS also offers the "go" statement as SQL Server Studio to execute a certain number of queries in a batch. However, the "go" statement needs to be on a separate line.

Example:

DROP TABLE ABC;
go
CREATE TABLE ABC(id INT);
Scoping to schemas

If you are using the operational mode "Database managed manually" you currently need to specify the schema for all table references.

Deleting objects if they exist
DROP_TABLE_IF_EXISTS stored procedure

Using the stored procedure "DROP_TABLE_IF_EXISTS" you can delete a table and if it does not exist you will not receive an error.

Example:

EXEC DROP_TABLE_IF_EXISTS 'ABC'; -- deletes the table ABC
DROP_VIEW_IF_EXISTS stored procedure

Using the stored procedure "DROP_VIEW_IF_EXISTS" you can delete a view and if it does not exist you will not receive an error.

Example:

EXEC DROP_VIEW_IF_EXISTS 'XYZ'; -- deletes the view XYZ
DROP_INDEX_IF_EXISTS stored procedure

Using the stored procedure "DROP_VIEW_IF_EXISTS" you can delete a view and if it does not exist you will not receive an error.

Example:

CREATE INDEX i1 ON ABC(COL1); -- creates an index i1 on column COL1 of table ABC (standard SQL syntax)
EXEC DROP_INDEX_IF_EXISTS 'ABC', 'i1'; -- deletes the index i1 in table ABC