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

點擊下載SQL Compare試用版

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

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

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

有什么問題呢？

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

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

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

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

合并

想象一下，我們想要一些代碼來確保一個表僅包含我們想要的數據，沒有更多，更少。我們使用MERGE語句，以便僅執行必要的插入或刪除操作。讓我們舉一個簡單的例子，來自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])

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

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

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

創建MERGE語句的數據集

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

出于懷舊之情，我們將在古老的NorthWind數據庫上進行嘗試，因為它甚至不會給微薄的文件系統帶來負擔。該腳本將執行#SaveMergeStatementFromTablePPP（因此，您首先需要創建PPP；請參閱后面的內容），并依次提供每個表的名稱作為源。對于sp_msforeachtable（和sp_msforeachdb）系統過程，SQL Server在表或數據庫的名稱中替換?您作為參數提供的字符串中的占位符（' '）。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

這將產生一個文件...

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

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

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

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

強大的程序

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

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

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

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

完善系統

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

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

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

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

相關產品推薦：

SQL Prompt：SQL語法提示工具

SQL Toolbelt：Red Gate產品套包

SQL Monitor：SQL Server監控工具