SQL Compare是一款比較和同步SQL Server數據庫結構的工具。現有超過150,000的數據庫管理員、開發人員和測試人員在使用它。當測試本地數據庫，暫存或激活遠程服務器的數據庫時，SQL Compare將分配數據庫的過程自動化。
點擊下載SQL Compare免費版
您要使用SQL Compare或SQL Change Automation（SCA）創建或更新數據庫，同時確保其數據符合預期。您希望避免每次都運行任何其他PowerShell腳本，并且希望將所有內容（包括數據）保留在源代碼控制中。您只想保持一切簡單。本文通過MERGE從存儲過程中生成腳本演示了它是如何完成的。
如果您沒有很多數據，或者只有幾個需要一些靜態數據的表，則可以使用SQL Compare或SCA輕松完成此操作，方法是添加一個部署后腳本，該腳本將作為同步的一部分。否則，它只是部署過程中要運行的一個額外腳本。
我們將使用之前的文章“將自定義部署腳本與SQL Compare或SQL Change Automation一起使用”中介紹的技術，該技術涉及運行一系列MERGE腳本以確保數據庫具有所需的數據。為了創建這些MERGE腳本，我使用了PPP（相當強大的過程），我將在這里進行介紹。手工編寫這些腳本很費力，盡管您幾乎不需要重復創建腳本的繁瑣工作，但是需要對它進行修改以響應相應表的更改。
那么，有什么問題呢？
如果沒有數據，那么重新設計數據庫將很簡單。無論我們在開發中延遲多久，當我們要部署到UAT、登臺和生產時，在保持數據完整的同時部署架構更改的問題始終會困擾我們。無論采用哪種方式來創建新版本的數據庫，都需要進行大量的重新設計，但始終會冒著在處理現有數據時遇到困難的風險，尤其是在拆分、合并或重命名表的情況下。
盡管我一直主張在整個開發過程中測試您的數據遷移腳本，但有時您只需要一個工作系統，該工作系統具有從源代碼控制構建的某些描述性數據。為此，我們有其他選擇：
通常，我們僅從源代碼進行一次構建，然后在開發和測試中將其與各種數據庫副本同步。通常，現代的關系數據庫知道在發出ALTER數據庫命令時如何保存數據，而同步工具（如SQL Compare）知道許多技巧，而SQL Server不能保存數據。但是，總有一段時間，由于不確定性太大，這變得不可能。那時，您需要使用T-SQL腳本將其拼寫出來，但是您可以這樣做。
我將描述的技術只能與“靜態數據”結合使用，而不會不斷更新。不過，這對于開發數據而言非常有用，因此它適合許多開發和測試工作。如果您希望使用相同的技術來同步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短語中附加一條語句，以便在必要時更新值，例如：
([WHEN MATCHED [AND ] THEN ] [...n])
您真的要為數據庫中的所有表手工編寫這樣的代碼嗎？不太可能。這是PPP的輸出，稱為#SaveMergeStatementFromTable，我將在后面顯示：
DECLARE @TheStatement NVARCHAR(MAX)
EXECUTE #SaveMergeStatementFromTable
@tablespec='Adventureworks2016.[HumanResources].[Department]',
@Statement=@TheStatement OUTPUT;
PRINT @TheStatement
它使用VALUES語句從@tablespec中指定的表中創建MERGE腳本，并使用VALUES語句創建數據源。它返回此腳本作為輸出變量。然后您可以執行它。這將確保在關鍵字段中具有正確值的行數正確。這是一個完整而美妙的解決方案嗎？不完全是。它適用于小型表，但是多行VALUES子句的伸縮性不好。最好將其分解為較小的語句。對于較大的表，您最終會達到需要對表進行截斷，然后使用本機BCP將數據導入其中的大小。如果希望執行MERGE，則將數據BCP到臨時表中，對其進行索引，然后將其用作MERGE的源，而不是使用表值構造器。
您還可以從文件中導入JSON或將其保存在腳本中，并將其用作表源。對于本文，我將堅持簡單性，并演示使用多行VALUES語句的原理，這些語句可以輕松地保留在源代碼控制中以構建開發數據庫或添加靜態數據。
創建MERGE語句的數據集
有不同的方法可以做到這一點。一種是使用來自SSMS的SQL。為此，您只需要對文件系統中為此類活動保留的部分進行讀寫訪問。對于該演示，我創建了一個目錄' c：\ data \ RawData '，并授予了SQL Server訪問權限。您還需要暫時允許使用xp_cmdshell。對于那些神經質的人，有時我還將提供一個可以代替使用的PowerShell腳本。
出于懷舊之情，我們將在古老的NorthWind數據庫上進行嘗試，因為它甚至不會給簡陋的文件系統帶來負擔。該腳本將執行#SaveMergeStatementFromTable 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
如果成功，它將給出以下信息：
如果要使用SQL Compare或SCA將這種數據部署合并到架構同步中，我們將只需要一個腳本。我們還需要在開始時禁用約束，并在完成后全部啟用它們。這需要對該方法進行非常小的修改，但是原理保持不變。
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
奇怪的是，當SQL Compare執行synch腳本時，如果您不小心，它可能會為在SSMS中執行該腳本時能正常工作的代碼提出錯誤。這解釋了為什么要逐個表禁用約束。這也解釋了為什么我避免使用GO批量分隔符。
這將產生一個文件...
如果直接在SSMS中執行此文件，它將檢查每個表并進行必要的插入和刪除操作，以確保數據相同。當添加到同步腳本后，它將對您創建或更改的數據庫執行相同的操作。
如果您希望使用其他名稱部署到數據庫，則可以將@DestinationDatabase可選參數設置為#SaveMergeStatementFromTable正在創建或同步的數據庫的名稱，正如我在前面的代碼中所演示的那樣。如果我需要一個依賴于先前腳本的腳本來提供數據庫上下文（SQL Compare和SCA會執行此操作），則可以通過將@DontSpecifyTheDatabase可選參數設置為1 來隱藏三部分名稱的數據庫部分。
現在我們有了文件，我們可以將其放在Source Control中。好的，我假裝在這里是因為我沒有開發Northwind。我所做的就是通過使用SQL Compare將Northwind數據庫與空目錄進行比較來創建源代碼管理目錄。然后，我已經添加了自定義腳本目錄及其部署后和部署前的子目錄。
使用真實的數據庫，MERGE只要修改表，就可以非常簡單地運行SQL來生成語句。合并語句依賴于它們作用于具有相同名稱的相同列數的表。如果不是，則會出現錯誤。如果您修改源腳本中的表，以使目標數據庫中的一個或多個表不同，則相應的同步后合并語句將需要符合新設計。
現在，我將合并腳本（TotalFile.sql）放入Post-Deployment目錄中。一旦開發了此技術，就可以通過指定適當的部署后目錄的正確路徑，通過SQL Batch文件或PowerShell腳本直接將文件寫入。
從那時起，我可以使用此目錄同步數據和架構。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 Compare正版授權，或了解更多產品信息請點擊
標簽：
本站文章除注明轉載外，均為本站原創或翻譯。歡迎任何形式的轉載，但請務必注明出處、不得修改原文相關鏈接，如果存在內容上的異議請郵件反饋至chenjj@fc6vip.cn
文章轉載自：
金喜正規買球 
25次
相關產品
正式授權
最新文章 
金喜正規買球相關的文章 
微信
在線咨詢
渝公網安備
50010702500608號
客服熱線