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

點擊下載SQL Compare試用版

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

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

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

有什么問題呢？

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

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

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

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

合并

想象一下，我們想要一些代碼來確保一個表僅包含我們想要的數(shù)據(jù)，沒有更多，更少。我們使用MERGE語句，以便僅執(zhí)行必要的插入或刪除操作。讓我們舉一個簡單的例子，來自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ù)庫中的所有表手工編寫這樣的代碼嗎？不見得。這是PPP的輸出，稱為#SaveMergeStatementFromTable，我將在后面顯示：

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

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

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

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

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

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

這將產生一個文件...

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

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

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

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

強大的程序

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

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

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

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

完善系統(tǒng)

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

首先，您可以填充已填充了所需測試數(shù)據(jù)的臨時表，然后添加主鍵。我們通過使用臨時表作為源的查詢將臨時表中的數(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;

進行SQL比較并發(fā)現(xiàn)數(shù)據(jù)和架構與源數(shù)據(jù)庫相同是令人驚訝的。如果數(shù)據(jù)庫中的表設計發(fā)生更改，則只需要準備一個新的部署后腳本，但是由于它都是自動進行的，因此我認為這反倒不會帶來太大麻煩。我使用多行VALUES語句是因為它看起來比使用JSON來保存數(shù)據(jù)要少一些，但是我認為使用JSON可以允許使用更大的表。

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

相關產品推薦：

SQL Prompt：SQL語法提示工具

SQL Toolbelt：Red Gate產品套包

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