CreateOrReplace 命令 (TMSL)
適用于：
SQL Server 2016 和更新版本的 Analysis Services Azure Analysis Services Fabric/Power BI Premium
建立或取代指定的物件和指定的所有子代物件。 建立不存在的物件。 現有的物件會取代為新的定義。
每當您指定讀寫屬性時，請務必全部包含這些屬性。 省略讀寫物件會被視為刪除。
要求
要求的結構會根據 物件而有所不同。 父系的物件必須包含其所有子系，雖然不需要同層級和父系 (的完整物件定義) 。
以已重新命名、最小資料庫定義取代現有的資料庫，以指定其名稱、修改的模型屬性和連接。 由於物件定義不包含資料表、資料分割或關聯性，因此會刪除所有這些物件。
{
"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 物件 (TMSL) 取代連接名稱。
{
"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 物件 (TMSL) 覆寫任何現有的資料表，只保留指定的資料表。
{
"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"
}
]
}
]
}
}
}
}
取代分割區名稱。 資料分割物件有三個讀寫屬性：名稱、來源、描述。 每當您指定讀寫屬性時，請務必全部包含這些屬性。 省略讀寫物件會被視為刪除。
因為物件定義是分割區，所以只會影響具名資料分割及其定義。 其他資料表、關聯性和分割區不會受到影響。
除非您正在建立、取代或改變數據源物件本身，否則腳本中所參考的任何資料來源 (，例如在下列資料分割腳本中，) 必須是模型中現有的 DataSource 物件。 如果您需要變更資料來源，請將它新增至
{
"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 物件 (TMSL) 將角色定義取代為包含成員的角色定義。
{
"createOrReplace": {
"object": {
"database": "AdventureWorksTabular1200",
"role": "DataReader"
},
"role": {
"name": "DataReader",
"modelPermission": "read",
"members": [
{
"memberName": "ADVENTUREWORKS\\InternalSalesGrp"
}
]
}
}
}
回應
當命令成功時，傳回空的結果。 否則，會傳回 XMLA 例外狀況。
範例
範例 1 - 建立新的資料庫，並覆寫相同名稱的現有資料庫。
{
"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"
}
]
}
}
}
}
使用量 (端點)
此命令元素用於 Execute 方法的語句中， (XMLA) 透過 XMLA 端點呼叫，以下列方式公開：
SQL SERVER MANAGEMENT STUDIO (SSMS) 中的 XMLA 視窗
作為 invoke-ascmd PowerShell Cmdlet 的輸入檔
作為 SSIS 工作或SQL Server Agent作業的輸入
您可以從 SSMS 為此命令產生現成的腳本。 例如，您可以以滑鼠右鍵按一下現有的資料庫 >腳本腳本>資料庫作為>CREATE 或 REPLACE To。
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應