CreateOrReplace command (TMSL)
Applies to: 
SQL Server 2016 and later Analysis Services
Azure Analysis Services
Fabric/Power BI Premium
Creates or replaces the specified object and all the descendant objects that are specified. Non-existent objects are created. Existing objects are replaced with the new definition.
Whenever you specify a read-write property, make sure to include them all. Omission of a read-write object is considered a deletion.
Request
The structure of the request varies based on the object. An object that is a parent must include all of its children, although the full object definitions of siblings and parent(s) are not required.
Replaces an existing database with a renamed, minimal database definition that specifies its name, modified model properties, and a connection. Because the object definition does not include tables, partitions, or relationships, all of those objects are deleted.
{
"createOrReplace": {
"object": {
"database": "AdventureWorksTabular1200"
},
"database": {
"name": "TestCreateOrReplaceDB",
"id": "newID",
"compatibilityLevel": 1200,
"model": {
"defaultMode": "import",
"culture": "en-US",
"dataSources": [
{
"name": "SqlServer localhost AdventureworksDW2016",
"connectionString": "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureworksDW2016;Integrated Security=SSPI;Persist Security Info=false",
"impersonationMode": "impersonateAccount",
"account": " ",
"annotations": [
{
"name": "ConnectionEditUISource",
"value": "SqlServer"
}
]
}
]
}
}
}
}
DataSources object (TMSL) Replaces a connection name.
{
"createOrReplace": {
"object": {
"database": "TestCreateOrReplaceDB",
"dataSource": "SqlServer localhost AdventureworksDW2016"
},
"dataSource": {
"name": "New connection name",
"connectionString": "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureworksDW2016;Integrated Security=SSPI;Persist Security Info=false",
"impersonationMode": "impersonateAccount",
"account": " ",
"annotations": [
{
"name": "ConnectionEditUISource",
"value": "SqlServer"
}
]
}
}
}
Tables object (TMSL) Overwrites any existing tables, leaving just the one specified.
{
"createOrReplace": {
"object": {
"database": "AdventureWorksTabular1200"
},
"database": {
"name": "AdventureWorksTabular1200",
"id": "New-AdventureWorksTabular1200",
"compatibilityLevel": 1200,
"model": {
"defaultMode": "import",
"culture": "en-US",
"dataSources": [
{
"name": "SqlServer localhost AdventureworksDW2016",
"connectionString": "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureworksDW2016;Integrated Security=SSPI;Persist Security Info=false",
"impersonationMode": "impersonateAccount",
"account": " ",
"annotations": [
{
"name": "ConnectionEditUISource",
"value": "SqlServer"
}
]
}
],
"tables": [
{
"name": "Date",
"columns": [
{
"name": "DateKey",
"dataType": "int64",
"sourceColumn": "DateKey",
"sourceProviderType": "Integer"
},
{
"name": "FullDateAlternateKey",
"dataType": "dateTime",
"sourceColumn": "FullDateAlternateKey",
"formatString": "General Date",
"sourceProviderType": "DBDate"
},
{
"name": "CalendarYear",
"dataType": "int64",
"sourceColumn": "CalendarYear",
"sourceProviderType": "SmallInt"
}
],
"partitions": [
{
"name": "DimDate",
"dataView": "full",
"source": {
"query": " SELECT [dbo].[DimDate].* FROM [dbo].[DimDate] ",
"dataSource": "SqlServer localhost AdventureworksDW2016"
}
}
],
"annotations": [
{
"name": "_TM_ExtProp_QueryDefinition",
"value": " SELECT [dbo].[DimDate].* FROM [dbo].[DimDate] "
},
{
"name": "_TM_ExtProp_DbTableName",
"value": "DimDate"
},
{
"name": "_TM_ExtProp_DbSchemaName",
"value": "dbo"
}
]
}
]
}
}
}
}
Replace a partition name. Partition objects have three read-write properties: name, source, description. Whenever you specify a read-write property, make sure to include them all. Omission of a read-write object is considered a deletion.
Because the object definition is the partition, only the named partition and its definition is impacted. Other tables, relationships, and partitions are unaffected.
Unless you are creating, replacing, or altering the data source object itself, any data source referenced in your script (such as in the partition script below) must be an existing DataSource object in your model. If you need to change the data source, add it to
{
"createOrReplace": {
"object": {
"database": "AdventureWorksTabular1200",
"table": "FactSalesQuota",
"partition": "FactSalesQuota - 2011"
},
"partition": {
"name": "Sales Quota for 2011",
"mode": "import",
"dataView": "full",
"source": {
"query": [
"SELECT [dbo].[FactSalesQuota].* FROM [dbo].[FactSalesQuota]",
"JOIN DimDate as DD",
"on DD.DateKey = FactSalesQuota.DateKey",
"WHERE DD.CalendarYear='2011'"
],
"dataSource": "SqlServer localhost AdventureworksDW2016"
}
}
}
}
Roles object (TMSL) Replaces a role definition with one that includes members.
{
"createOrReplace": {
"object": {
"database": "AdventureWorksTabular1200",
"role": "DataReader"
},
"role": {
"name": "DataReader",
"modelPermission": "read",
"members": [
{
"memberName": "ADVENTUREWORKS\\InternalSalesGrp"
}
]
}
}
}
Response
Returns an empty result when the command succeeds. Otherwise, an XMLA exception is returned.
Examples
Example 1 - Creates a new database, overwriting an existing database of the same name.
{
"createOrReplace": {
"object": {
"database": "AdventureWorksTabular1200"
},
"database": {
"name": "AdventureWorksTabular1200",
"id": "AdventureWorksTabular1200",
"compatibilityLevel": 1200,
"model": {
"defaultMode": "directQuery",
"culture": "en-US",
"dataSources": [
{
"name": "SqlServer localhost AdventureworksDW2016",
"connectionString": "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureworksDW2016;Integrated Security=SSPI;Persist Security Info=false",
"impersonationMode": "impersonateAccount",
"account": " ",
"annotations": [
{
"name": "ConnectionEditUISource",
"value": "SqlServer"
}
]
}
],
"tables": [
{
"name": "DimDate",
"columns": [
{
"name": "DateKey",
"dataType": "int64",
"sourceColumn": "DateKey",
"sourceProviderType": "Integer"
},
{
"name": "FullDateAlternateKey",
"dataType": "dateTime",
"sourceColumn": "FullDateAlternateKey",
"formatString": "General Date",
"sourceProviderType": "DBDate"
},
{
"name": "CalendarYear",
"dataType": "int64",
"sourceColumn": "CalendarYear",
"sourceProviderType": "SmallInt"
}
],
"partitions": [
{
"name": "DimDate",
"dataView": "full",
"source": {
"query": " SELECT [dbo].[DimDate].* FROM [dbo].[DimDate] ",
"dataSource": "SqlServer localhost AdventureworksDW2016"
}
}
],
"annotations": [
{
"name": "_TM_ExtProp_QueryDefinition",
"value": " SELECT [dbo].[DimDate].* FROM [dbo].[DimDate] "
},
{
"name": "_TM_ExtProp_DbTableName",
"value": "DimDate"
},
{
"name": "_TM_ExtProp_DbSchemaName",
"value": "dbo"
}
]
},
{
"name": "DimEmployee",
"columns": [
{
"name": "EmployeeKey",
"dataType": "int64",
"sourceColumn": "EmployeeKey",
"sourceProviderType": "Integer"
},
{
"name": "SalesTerritoryKey",
"dataType": "int64",
"sourceColumn": "SalesTerritoryKey",
"sourceProviderType": "Integer"
},
{
"name": "FirstName",
"dataType": "string",
"sourceColumn": "FirstName",
"sourceProviderType": "WChar"
},
{
"name": "LastName",
"dataType": "string",
"sourceColumn": "LastName",
"sourceProviderType": "WChar"
},
{
"name": "MiddleName",
"dataType": "string",
"sourceColumn": "MiddleName",
"sourceProviderType": "WChar"
},
{
"name": "SalesPersonFlag",
"dataType": "boolean",
"sourceColumn": "SalesPersonFlag",
"formatString": "\"TRUE\";\"TRUE\";\"FALSE\"",
"sourceProviderType": "Boolean"
},
{
"name": "DepartmentName",
"dataType": "string",
"sourceColumn": "DepartmentName",
"sourceProviderType": "WChar"
}
],
"partitions": [
{
"name": "DimEmployee",
"dataView": "full",
"source": {
"query": " SELECT [dbo].[DimEmployee].* FROM [dbo].[DimEmployee] ",
"dataSource": "SqlServer localhost AdventureworksDW2016"
}
}
],
"annotations": [
{
"name": "_TM_ExtProp_QueryDefinition",
"value": " SELECT [dbo].[DimEmployee].* FROM [dbo].[DimEmployee] "
},
{
"name": "_TM_ExtProp_DbTableName",
"value": "DimEmployee"
},
{
"name": "_TM_ExtProp_DbSchemaName",
"value": "dbo"
}
]
},
{
"name": "FactSalesQuota",
"columns": [
{
"name": "SalesQuotaKey",
"dataType": "int64",
"sourceColumn": "SalesQuotaKey",
"sourceProviderType": "Integer"
},
{
"name": "EmployeeKey",
"dataType": "int64",
"sourceColumn": "EmployeeKey",
"sourceProviderType": "Integer"
},
{
"name": "DateKey",
"dataType": "int64",
"sourceColumn": "DateKey",
"sourceProviderType": "Integer"
},
{
"name": "CalendarYear",
"dataType": "int64",
"sourceColumn": "CalendarYear",
"sourceProviderType": "SmallInt"
},
{
"name": "SalesAmountQuota",
"dataType": "decimal",
"sourceColumn": "SalesAmountQuota",
"formatString": "\\$#,0.00;(\\$#,0.00);\\$#,0.00",
"sourceProviderType": "Currency",
"annotations": [
{
"name": "Format",
"value": "\<Format Format=\"Currency\" Accuracy=\"2\" ThousandSeparator=\"True\">\<Currency LCID=\"1033\" DisplayName=\"$ English (United States)\" Symbol=\"$\" PositivePattern=\"0\" NegativePattern=\"0\" /></Format>"
}
]
},
{
"name": "Date",
"dataType": "dateTime",
"sourceColumn": "Date",
"formatString": "General Date",
"sourceProviderType": "DBTimeStamp"
}
],
"partitions": [
{
"name": "FactSalesQuota",
"dataView": "full",
"source": {
"query": " SELECT [dbo].[FactSalesQuota].* FROM [dbo].[FactSalesQuota] ",
"dataSource": "SqlServer localhost AdventureworksDW2016"
}
},
{
"name": "FactSalesQuota - 2011",
"mode": "import",
"dataView": "sample",
"source": {
"query": [
"SELECT [dbo].[FactSalesQuota].* FROM [dbo].[FactSalesQuota]",
"JOIN DimDate as DD",
"on DD.DateKey = FactSalesQuota.DateKey",
"WHERE DD.CalendarYear='2011'"
],
"dataSource": "SqlServer localhost AdventureworksDW2016"
},
"annotations": [
{
"name": "QueryEditorSerialization",
"value": [
"\<?xml version=\"1.0\" encoding=\"UTF-16\"?>\<Gemini xmlns=\"QueryEditorSerialization\"><AnnotationContent><![CDATA[<RSQueryCommandText>SELECT [dbo].[FactSalesQuota].* FROM [dbo].[FactSalesQuota]",
"JOIN DimDate as DD",
"on DD.DateKey = FactSalesQuota.DateKey",
"WHERE DD.CalendarYear='2011'</RSQueryCommandText><RSQueryCommandType>Text</RSQueryCommandType><RSQueryDesignState></RSQueryDesignState>]]></AnnotationContent></Gemini>"
]
}
]
}
],
"annotations": [
{
"name": "_TM_ExtProp_QueryDefinition",
"value": " SELECT [dbo].[FactSalesQuota].* FROM [dbo].[FactSalesQuota] "
},
{
"name": "_TM_ExtProp_DbTableName",
"value": "FactSalesQuota"
},
{
"name": "_TM_ExtProp_DbSchemaName",
"value": "dbo"
}
]
}
],
"relationships": [
{
"name": "4426b078-193f-4a59-bc52-33f990bfb7da",
"fromTable": "FactSalesQuota",
"fromColumn": "DateKey",
"toTable": "DimDate",
"toColumn": "DateKey"
},
{
"name": "cde1e139-4553-4d0a-a025-1cd98e35aab2",
"fromTable": "FactSalesQuota",
"fromColumn": "EmployeeKey",
"toTable": "DimEmployee",
"toColumn": "EmployeeKey"
}
]
}
}
}
}
Usage (endpoints)
This command element is used in a statement of the Execute Method (XMLA) call over an XMLA endpoint, exposed in the following ways:
As an XMLA window in SQL Server Management Studio (SSMS)
As an input file to the invoke-ascmd PowerShell cmdlet
As an input to an SSIS task or SQL Server Agent job
You can generate a ready-made script for this command from SSMS. For example, you can right-click an existing database > Script > Script Database as > CREATE or REPLACE To.