SQL Compare是一款比較和同步SQL Server數(shù)據(jù)庫(kù)結(jié)構(gòu)的工具。現(xiàn)有超過150,000的數(shù)據(jù)庫(kù)管理員、開發(fā)人員和測(cè)試人員在使用它。當(dāng)測(cè)試本地?cái)?shù)據(jù)庫(kù)，暫存或激活遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫(kù)時(shí)，SQL Compare將分配數(shù)據(jù)庫(kù)的過程自動(dòng)化。

點(diǎn)擊下載SQL Compare試用版

您想使用SQL Compare或SQL Change Automation（SCA）創(chuàng)建或更新數(shù)據(jù)庫(kù)，同時(shí)確保其數(shù)據(jù)符合您的期望。您希望避免每次都運(yùn)行任何其他PowerShell腳本，并且希望將所有內(nèi)容（包括數(shù)據(jù)）保持在源代碼控制中。您只想讓一切簡(jiǎn)單。Phil Factor通過MERGE從存儲(chǔ)過程中生成腳本演示了它是如何完成的。

如果沒有很多數(shù)據(jù)，或者只有幾個(gè)需要一些靜態(tài)數(shù)據(jù)的表，則可以使用SQL Compare或SCA輕松完成此操作，方法是添加一個(gè)部署后腳本，該腳本將作為同步。否則，它只是部署過程中要運(yùn)行的一個(gè)額外腳本。

我們將使用上一篇文章將自定義部署腳本與SQL Compare或SQL Change Automation結(jié)合使用中描述的技術(shù)，該技術(shù)涉及運(yùn)行一系列MERGE腳本以確保數(shù)據(jù)庫(kù)具有所需的數(shù)據(jù)。為了創(chuàng)建這些MERGE腳本，我使用了PPP（相當(dāng)強(qiáng)大的過程），我將在這里進(jìn)行介紹。手工編寫這些腳本很費(fèi)力，盡管您幾乎不需要重復(fù)創(chuàng)建腳本的繁瑣工作，但是需要對(duì)它進(jìn)行修改以響應(yīng)相應(yīng)表的更改。

有什么問題呢？

如果沒有數(shù)據(jù)，那么重新設(shè)計(jì)數(shù)據(jù)庫(kù)將很簡(jiǎn)單。無(wú)論我們拖延了繁瑣的開發(fā)工作，當(dāng)我們要部署到UAT，登臺(tái)和生產(chǎn)時(shí)，在保持?jǐn)?shù)據(jù)完整的同時(shí)部署架構(gòu)更改的問題始終會(huì)困擾我們。在進(jìn)行大量的重新設(shè)計(jì)之后，無(wú)論選擇哪種方式創(chuàng)建數(shù)據(jù)庫(kù)的新版本，我們總是冒著在處理現(xiàn)有數(shù)據(jù)時(shí)遇到困難的風(fēng)險(xiǎn)，尤其是在拆分，合并或重命名表的情況下。

盡管我一直主張?jiān)谡麄€(gè)開發(fā)過程中測(cè)試您的數(shù)據(jù)遷移腳本，但有時(shí)您只需要一個(gè)帶有某些描述的數(shù)據(jù)的工作系統(tǒng)，該數(shù)據(jù)是從源代碼控制構(gòu)建的。為此，我們有其他選擇：

1. 構(gòu)建元數(shù)據(jù)并隨后使用BCP導(dǎo)入數(shù)據(jù)（請(qǐng)參閱使用SQL Change Automation從頭開始創(chuàng)建數(shù)據(jù)庫(kù)）。
2. 作為構(gòu)建的一部分，在受影響的表中將數(shù)據(jù)從舊模式“遷移”到新表（在基于狀態(tài)的數(shù)據(jù)庫(kù)部署過程中處理棘手的數(shù)據(jù)遷移）
3. 使用部署前和部署后腳本處理該問題，使DML活動(dòng)與用于構(gòu)建數(shù)據(jù)庫(kù)的DDL保持牢固的距離。（將自定義部署腳本與SQL Compare或SQL Change Automation一起使用）

我將描述的技術(shù)只能與“靜態(tài)數(shù)據(jù)”一起使用，而不會(huì)不斷更新。不過，這對(duì)于開發(fā)人員數(shù)據(jù)非常有用，因此它適合許多開發(fā)和測(cè)試工作。如果您希望使用相同的技術(shù)來同步UAT，登臺(tái)或生產(chǎn)（例如在拆分表之后），則需要通過剪切所有其他連接來“使數(shù)據(jù)庫(kù)靜默”，并在您進(jìn)行數(shù)據(jù)動(dòng)態(tài)合并時(shí)做分裂。我將在另一篇文章中解釋如何執(zhí)行此操作。

合并

想象一下，我們想要一些代碼來確保一個(gè)表僅包含我們想要的數(shù)據(jù)，沒有更多，更少。我們使用MERGE語(yǔ)句，以便僅執(zhí)行必要的插入或刪除操作。讓我們舉一個(gè)簡(jiǎn)單的例子，來自AdventureWorks：

SET IDENTITY_INSERT [Adventureworks2016].[HumanResources].[Department] ON;
MERGE INTO [Adventureworks2016].[HumanResources].[Department] AS target
  USING (VALUES (1, 'Engineering', 'Research and Development', '2008-04-30T00:00:00'),
(2, 'Tool Design', 'Research and Development', '2008-04-30T00:00:00'),
(3, 'Sales', 'Sales and Marketing', '2008-04-30T00:00:00'),
(4, 'Marketing', 'Sales and Marketing', '2008-04-30T00:00:00'),
(5, 'Purchasing', 'Inventory Management', '2008-04-30T00:00:00'),
(6, 'Research and Development', 'Research and Development', '2008-04-30T00:00:00'),
(7, 'Production', 'Manufacturing', '2008-04-30T00:00:00'),
(8, 'Production Control', 'Manufacturing', '2008-04-30T00:00:00'),
(9, 'Human Resources', 'Executive General and Administration', '2008-04-30T00:00:00'),
(10, 'Finance', 'Executive General and Administration', '2008-04-30T00:00:00'),
(11, 'Information Services', 'Executive General and Administration', '2008-04-30T00:00:00'),
(12, 'Document Control', 'Quality Assurance', '2008-04-30T00:00:00'),
(13, 'Quality Assurance', 'Quality Assurance', '2008-04-30T00:00:00'),
(14, 'Facilities and Maintenance', 'Executive General and Administration', '2008-04-30T00:00:00'),
(15, 'Shipping and Receiving', 'Inventory Management', '2008-04-30T00:00:00'),
(16, 'Executive', 'Executive General and Administration', '2008-04-30T00:00:00')
)source(DepartmentID, Name, GroupName, ModifiedDate)
ON source.DepartmentID = target.DepartmentID
WHEN NOT MATCHED BY TARGET THEN
  INSERT ( DepartmentID, Name, GroupName, ModifiedDate )
  VALUES ( DepartmentID, Name, GroupName, ModifiedDate )
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;
SET IDENTITY_INSERT [Adventureworks2016].[HumanResources].[Department] OFF;

([WHEN MATCHED [AND <clause_search_condition>] THEN <merge_matched> ] [...n])

您真的要為數(shù)據(jù)庫(kù)中的所有表手工編寫這樣的代碼嗎？不見得。這是PPP的輸出，稱為#SaveMergeStatementFromTable，我將在后面顯示：

   DECLARE @TheStatement NVARCHAR(MAX)
   EXECUTE #SaveMergeStatementFromTable 
     @tablespec='Adventureworks2016.[HumanResources].[Department]',
     @Statement=@TheStatement OUTPUT;
     PRINT @TheStatement

您還可以從文件或腳本中保存JSON，并將其用作表源。對(duì)于本文，我將堅(jiān)持簡(jiǎn)單性，并演示使用多行VALUES語(yǔ)句的原理，這些語(yǔ)句可以輕松地保留在源代碼控制中以構(gòu)建開發(fā)數(shù)據(jù)庫(kù)或添加靜態(tài)數(shù)據(jù)。

創(chuàng)建MERGE語(yǔ)句的數(shù)據(jù)集

有不同的方法可以做到這一點(diǎn)。一種是使用來自SSMS的SQL。為此，您只需要對(duì)文件系統(tǒng)中為此類活動(dòng)保留的部分進(jìn)行讀寫訪問。對(duì)于該演示，我創(chuàng)建了一個(gè)目錄' c：\ data \ RawData '，并授予了SQL Server訪問權(quán)限。您還需要暫時(shí)允許使用xp_cmdshell。對(duì)于那些神經(jīng)質(zhì)的人，有時(shí)我還將提供一個(gè)可以代替使用的PowerShell腳本。

出于懷舊之情，我們將在古老的NorthWind數(shù)據(jù)庫(kù)上進(jìn)行嘗試，因?yàn)樗踔敛粫?huì)給微薄的文件系統(tǒng)帶來負(fù)擔(dān)。該腳本將執(zhí)行#SaveMergeStatementFromTablePPP（因此，您首先需要?jiǎng)?chuàng)建PPP；請(qǐng)參閱后面的內(nèi)容），并依次提供每個(gè)表的名稱作為源。對(duì)于sp_msforeachtable（和sp_msforeachdb）系統(tǒng)過程，SQL Server在表或數(shù)據(jù)庫(kù)的名稱中替換?您作為參數(shù)提供的字符串中的占位符（' '）。PPP會(huì)MERGE在每種情況下（@CreatedScript）生成相應(yīng)的語(yǔ)句。對(duì)于每個(gè)表，我們使用其MERGE語(yǔ)句填充臨時(shí)表（##myTemp），然后將其寫到指定目錄中的自己的文件中（不要錯(cuò)過路徑中的尾隨反沖）：

USE northwind
DECLARE @ourPath sysname ='C:\data\RawData\Northwind\MergeData\';
DECLARE @TheServer sysname =@@ServerName
Declare @command NVARCHAR(4000)= '
print ''Creating SQL Merge file for ?''
DECLARE @CreatedScript NVARCHAR(MAX)
EXECUTE #SaveMergeStatementFromTable  @TableSpec=''?'', @Statement=@CreatedScript OUTPUT
CREATE TABLE ##myTemp (Bulkcol nvarchar(MAX))
INSERT INTO ##myTemp (Bulkcol) SELECT @CreatedScript
print ''Writing out ?''
EXECUTE xp_cmdshell ''bcp ##myTemp out '+@ourPath+'?.SQL -c -C 65001 -T -S '+@TheServer+' ''
DROP TABLE ##myTemp'
EXECUTE sp_msforeachtable @command
GO

USE northwind
DECLARE @TotalScript NVARCHAR(MAX)
DECLARE @DestinationDatabase sysname='WestWind'
DECLARE @ourPath sysname ='C:\data\RawData\Northwind\MergeData\TotalFile.sql';
DECLARE @TheServer sysname =@@ServerName
DROP TABLE IF exists ##myTemp
CREATE TABLE ##myTemp (Bulkcol nvarchar(MAX))
DECLARE @DisableConstraints nvarchar(4000)='Print ''Disabling all table constraints''
'
SELECT @DisableConstraints=@DisableConstraints+'ALTER TABLE [WestWind].[dbo].'+QuoteName(name)+' NOCHECK CONSTRAINT ALL
' FROM sys.tables
INSERT INTO ##myTemp (BulkCol) SELECT @DisableConstraints
DECLARE @command NVARCHAR(4000)= '
print ''Creating SQL Merge file for ?''
DECLARE @CreatedScript NVARCHAR(MAX)
EXECUTE #SaveMergeStatementFromTable  @TableSpec=''?'',@DestinationDatabase='''+@DestinationDatabase+''',@Statement=@CreatedScript OUTPUT
INSERT INTO ##myTemp (Bulkcol) SELECT coalesce(@CreatedScript,'''')+''
''
'
SELECT @command
EXECUTE sp_msforeachtable @command
print 'Writing out file'
DECLARE @BCPCommand NVARCHAR(4000)='bcp ##myTemp out '+@ourPath+' -c -C 65001 -T -S '+@TheServer
EXECUTE xp_cmdshell @BCPCommand
DECLARE @endCommand VARCHAR(4000)= (SELECT 'ECHO EXEC sp_msforeachtable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'' >>'+@ourPath)
EXECUTE xp_cmdshell @endCommand
DROP TABLE ##myTemp

這將產(chǎn)生一個(gè)文件...

如果您希望使用其他名稱部署到數(shù)據(jù)庫(kù)，則可以將@DestinationDatabase可選參數(shù)設(shè)置為#SaveMergeStatementFromTable正在創(chuàng)建或同步的數(shù)據(jù)庫(kù)的名稱，正如我在前面的代碼中所演示的那樣。如果我需要一個(gè)依賴于先前腳本的腳本來提供數(shù)據(jù)庫(kù)上下文（SQL Compare和SCA會(huì)執(zhí)行此操作），則可以通過將@DontSpecifyTheDatabase可選參數(shù)設(shè)置為1 來隱藏三部分名稱的數(shù)據(jù)庫(kù)部分。

現(xiàn)在我們有了文件，可以將其放入“源代碼管理”中。好的，我在這里是假裝的，因?yàn)槲覜]有開發(fā)Northwind。我要做的就是通過使用SQL Compare將Northwind數(shù)據(jù)庫(kù)與空目錄進(jìn)行比較來創(chuàng)建源代碼管理目錄。然后，我已經(jīng)添加了自定義腳本目錄及其部署后和部署前的子目錄。

使用真實(shí)的數(shù)據(jù)庫(kù)，MERGE只要修改表，就可以非常簡(jiǎn)單地運(yùn)行SQL來生成語(yǔ)句。合并語(yǔ)句依賴于它們作用于具有相同名稱的相同列數(shù)的表。如果不是，將出現(xiàn)錯(cuò)誤。如果您修改源腳本中的表，以使目標(biāo)數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)表不同，則相應(yīng)的同步后合并語(yǔ)句將需要符合新設(shè)計(jì)。

從那時(shí)起，我可以使用此目錄同步數(shù)據(jù)和架構(gòu)。SQL Compare會(huì)將腳本追加到它生成的生成腳本的末尾，并使用它來填充表。請(qǐng)注意，在數(shù)據(jù)填充操作之前禁用約束并在之后啟用約束很重要。如果您有意在源代碼管理的對(duì)象構(gòu)建腳本中禁用了約束，則可能導(dǎo)致問題，但是我們將忽略該問題。

強(qiáng)大的程序

我使用了臨時(shí)過程來完成創(chuàng)建MERGE腳本中的驢工作。這是我用來提取數(shù)據(jù)的一系列過程中的一個(gè)，我在//github.com/Phil-Factor/JSONSQLServerRoutines上維護(hù)這些過程。

如果指定表，則此PPP將創(chuàng)建一條MERGE語(yǔ)句。它必須在SQL Server 2017或更高版本上運(yùn)行，因?yàn)樗褂昧嗽揝TRING_AGG功能。如果使用XML串聯(lián)技巧，可以將其更改為在SQL Server 2016上運(yùn)行。

它使用VALUES包含表中數(shù)據(jù)的多行語(yǔ)句創(chuàng)建表源，并將其用作MERGE語(yǔ)句源，然后可以在提供其名稱的目標(biāo)表上執(zhí)行該語(yǔ)句。如所討論的，該技術(shù)僅對(duì)小表才可行，因?yàn)槭褂迷揤ALUES子句意味著該子句隨比例降低。

源表或查詢可以通過的' database.schema.table'格式指定@tablespec，也可以通過分別提供表名稱，模式和數(shù)據(jù)庫(kù)來指定。您可以提供查詢，盡管在這種情況下，您將需要提供目標(biāo)表的名稱。您的查詢將提供源數(shù)據(jù)，并且必須以正確的順序?yàn)槟?指定的目標(biāo)表生成正確的表源，并以正確的順序顯示正確的列。

完善系統(tǒng)

除了表名，您還可以向該例程提供查詢。該查詢必須產(chǎn)生與目標(biāo)表相同的結(jié)果，當(dāng)然不包括計(jì)算列。這有效地使您可以指定例如如何填充表拆分的結(jié)果表。通常，MERGE我們通過傳遞給過程的表來確定主鍵。但是，查詢結(jié)果不能有主鍵。您可以通過兩種方法解決此問題。

首先，您可以填充已填充了所需測(cè)試數(shù)據(jù)的臨時(shí)表，然后添加主鍵。我們通過使用臨時(shí)表作為源的查詢將臨時(shí)表中的數(shù)據(jù)傳遞給過程：

USE AdventureWorks2016;
SELECT TOP 100 Customer.PersonID, AccountNumber,
  Identity(INT, 1, 1) AS uniquifier, PersonType, Title, FirstName, MiddleName,
  LastName, Suffix, AddressLine1, AddressLine2, City, PostalCode, Name
  INTO #tempTable
  FROM Sales.Customer
    INNER JOIN Person.Person
      ON Customer.PersonID = Person.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress
      ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
    INNER JOIN Person.Address
      ON BusinessEntityAddress.AddressID = Address.AddressID
    INNER JOIN Person.AddressType
      ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;
ALTER TABLE #tempTable ALTER COLUMN PersonID INTEGER NOT NULL;
ALTER TABLE #tempTable
ADD CONSTRAINT MyTempPKConstraint PRIMARY KEY CLUSTERED
      (PersonID, AccountNumber, uniquifier);
DECLARE @TheStatement NVARCHAR(MAX);
EXECUTE #SaveMergeStatementFromTable @Query = 'Select top 100 * from #tempTable',
  @Destination = 'MyTempTable', @Statement = @TheStatement OUTPUT;
PRINT @TheStatement;
DROP TABLE #tempTable;

USE AdventureWorks2016;
DECLARE @TheStatement NVARCHAR(MAX);
EXECUTE #SaveMergeStatementFromTable 
  @Query = '
  SELECT top 10 Customer.PersonID, AccountNumber,
  PersonType, Title, FirstName, MiddleName,
  LastName, Suffix, AddressLine1, AddressLine2, City, PostalCode, Name
  FROM Sales.Customer
    INNER JOIN Person.Person
      ON Customer.PersonID = Person.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress
      ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
    INNER JOIN Person.Address
      ON BusinessEntityAddress.AddressID = Address.AddressID
    INNER JOIN Person.AddressType
      ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID',
  @Destination = 'MyTempTable', 
  @PrimaryKeys='PersonID, AccountNumber',
  @Statement = @TheStatement OUTPUT;
PRINT @TheStatement;

進(jìn)行SQL比較并發(fā)現(xiàn)數(shù)據(jù)和架構(gòu)與源數(shù)據(jù)庫(kù)相同是令人驚訝的。如果數(shù)據(jù)庫(kù)中的表設(shè)計(jì)發(fā)生更改，則只需要準(zhǔn)備一個(gè)新的部署后腳本，但是由于它都是自動(dòng)進(jìn)行的，因此我認(rèn)為這反倒不會(huì)帶來太大麻煩。我使用多行VALUES語(yǔ)句是因?yàn)樗雌饋肀仁褂肑SON來保存數(shù)據(jù)要少一些，但是我認(rèn)為使用JSON可以允許使用更大的表。

為什么不只使用本地BCP來存儲(chǔ)表？按照我在此處演示的方法進(jìn)行操作，意味著普通的SQL Compare或SCA部署可以完成此操作而無(wú)需其他腳本。一切都由數(shù)據(jù)庫(kù)腳本保存。

相關(guān)產(chǎn)品推薦：

SQL Prompt：SQL語(yǔ)法提示工具

SQL Toolbelt：Red Gate產(chǎn)品套包

SQL Monitor：SQL Server監(jiān)控工具