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免費(fèi)版

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

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

我們將使用之前的文章“將自定義部署腳本與SQL Compare或SQL Change Automation一起使用”中介紹的技術(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)單。無論我們?cè)陂_發(fā)中延遲多久，當(dāng)我們要部署到UAT、登臺(tái)和生產(chǎn)時(shí)，在保持?jǐn)?shù)據(jù)完整的同時(shí)部署架構(gòu)更改的問題始終會(huì)困擾我們。無論采用哪種方式來創(chuàng)建新版本的數(shù)據(jù)庫(kù)，都需要進(jìn)行大量的重新設(shè)計(jì)，但始終會(huì)冒著在處理現(xiàn)有數(shù)據(jù)時(shí)遇到困難的風(fēng)險(xiǎn)，尤其是在拆分、合并或重命名表的情況下。

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

• 構(gòu)建元數(shù)據(jù)并隨后使用BCP導(dǎo)入數(shù)據(jù)。

• 作為構(gòu)建的一部分，將數(shù)據(jù)從受影響的表中的舊模式“遷移”到新表

• 處理部署前和部署后腳本的問題，使DML活動(dòng)與用于構(gòu)建數(shù)據(jù)庫(kù)的DDL保持牢固的距離。

通常，我們僅從源代碼進(jìn)行一次構(gòu)建，然后在開發(fā)和測(cè)試中將其與各種數(shù)據(jù)庫(kù)副本同步。通常，現(xiàn)代的關(guān)系數(shù)據(jù)庫(kù)知道在發(fā)出ALTER數(shù)據(jù)庫(kù)命令時(shí)如何保存數(shù)據(jù)，而同步工具（如SQL Compare）知道許多技巧，而SQL Server不能保存數(shù)據(jù)。但是，總有一段時(shí)間，由于不確定性太大，這變得不可能。那時(shí)，您需要使用T-SQL腳本將其拼寫出來，但是您可以這樣做。

我將描述的技術(shù)只能與“靜態(tài)數(shù)據(jù)”結(jié)合使用，而不會(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í)做分裂。

合并

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

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;

該語(yǔ)句將確保基于主鍵的值在每一行都有條目。對(duì)于開發(fā)工作，我們不太在乎非關(guān)鍵列是否不同。如果還需要確保其他列中的值相同，則需要在WHEN MATCHED短語(yǔ)中附加一條語(yǔ)句，以便在必要時(shí)更新值，例如：

([WHEN MATCHED [AND ] THEN  ] [...n])

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

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

它使用VALUES語(yǔ)句從@tablespec中指定的表中創(chuàng)建MERGE腳本，并使用VALUES語(yǔ)句創(chuàng)建數(shù)據(jù)源。它返回此腳本作為輸出變量。然后您可以執(zhí)行它。這將確保在關(guān)鍵字段中具有正確值的行數(shù)正確。這是一個(gè)完整而美妙的解決方案嗎？不完全是。它適用于小型表，但是多行VALUES子句的伸縮性不好。最好將其分解為較小的語(yǔ)句。對(duì)于較大的表，您最終會(huì)達(dá)到需要對(duì)表進(jìn)行截?cái)啵缓笫褂帽緳C(jī)BCP將數(shù)據(jù)導(dǎo)入其中的大小。如果希望執(zhí)行MERGE，則將數(shù)據(jù)BCP到臨時(shí)表中，對(duì)其進(jìn)行索引，然后將其用作MERGE的源，而不是使用表值構(gòu)造器。

您還可以從文件中導(dǎo)入JSON或?qū)⑵浔４嬖谀_本中，并將其用作表源。對(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ì)給簡(jiǎn)陋的文件系統(tǒng)帶來負(fù)擔(dān)。該腳本將執(zhí)行#SaveMergeStatementFromTable PPP（因此，您首先需要?jiǎng)?chuàng)建它），并依次提供每個(gè)表的名稱作為源。 對(duì)于sp_msforeachtable（和sp_msforeachdb）系統(tǒng)過程，SQL Server會(huì)在表或數(shù)據(jù)庫(kù)的名稱中替換您作為參數(shù)提供的字符串中的占位符（？）。在每種情況下，PPP都會(huì)生成相應(yīng)的MERGE語(yǔ)句（@CreatedScript）。 對(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

如果成功，它將給出以下信息：

如果要使用SQL Compare或SCA將這種數(shù)據(jù)部署合并到架構(gòu)同步中，我們將只需要一個(gè)腳本。我們還需要在開始時(shí)禁用約束，并在完成后全部啟用它們。這需要對(duì)該方法進(jìn)行非常小的修改，但是原理保持不變。

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

奇怪的是，當(dāng)SQL Compare執(zhí)行synch腳本時(shí)，如果您不小心，它可能會(huì)為在SSMS中執(zhí)行該腳本時(shí)能正常工作的代碼提出錯(cuò)誤。這解釋了為什么要逐個(gè)表禁用約束。這也解釋了為什么我避免使用GO批量分隔符。

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

如果直接在SSMS中執(zhí)行此文件，它將檢查每個(gè)表并進(jìn)行必要的插入和刪除操作，以確保數(shù)據(jù)相同。當(dāng)添加到同步腳本后，它將對(duì)您創(chuàng)建或更改的數(shù)據(jù)庫(kù)執(zhí)行相同的操作。

如果您希望使用其他名稱部署到數(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)在我們有了文件，我們可以將其放在Source Control中。好的，我假裝在這里是因?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ù)的表。如果不是，則會(huì)出現(xiàn)錯(cuò)誤。如果您修改源腳本中的表，以使目標(biāo)數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)表不同，則相應(yīng)的同步后合并語(yǔ)句將需要符合新設(shè)計(jì)。

現(xiàn)在，我將合并腳本（TotalFile.sql）放入Post-Deployment目錄中。一旦開發(fā)了此技術(shù)，就可以通過指定適當(dāng)?shù)牟渴鸷竽夸浀恼_路徑，通過SQL Batch文件或PowerShell腳本直接將文件寫入。

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

強(qiáng)大的程序

我使用了臨時(shí)過程來完成創(chuàng)建MERGE腳本中的復(fù)雜工作。這是我用來提取數(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ù)僅適用于小型表，因?yàn)槭褂迷揤ALUES子句意味著該子句隨比例降低。

源表或查詢可以通過的' database.schema.table'格式指定@tablespec，也可以通過分別提供表名稱、模式和數(shù)據(jù)庫(kù)來指定。您可以改為提供查詢，盡管在這種情況下，您將需要提供目標(biāo)表的名稱。您的查詢將提供源數(shù)據(jù)，并且必須為您指定的目標(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;

或者，您可以根據(jù)需要指定要使用的主鍵。

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;

結(jié)論

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

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

想要購(gòu)買SQL Compare正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊