Find Table Differences And Sync Tables

Have two databases with the same table schema and need to keep both up to date?

First, let us look at the differences between the two databases. The query below will show the differences between the development database table tbl_zip_code and the production database table tbl_zip_code. Ideally the differences should be minimal but the query will show all differences.

In this Query the Development table results will be on the top and the Production table results will be on the bottom. If desired you can uncomment the last line and the results will list in ID order having the table results potentially easier to see differences.

SELECT 'Dev' AS [TableName]
	,*
FROM (
	SELECT *
	FROM DevDB.[tbl_zip_code]

	EXCEPT

	SELECT *
	FROM Production.[tbl_zip_code]
	) AS T

UNION ALL

SELECT 'Prod' AS [TableName]
	,*
FROM (
	SELECT *
	FROM Production.[tbl_zip_code]

	EXCEPT

	SELECT *
	FROM DevDB.[tbl_zip_code]
	) AS T
--ORDER BY id

Now that we know the differences we can make any necessary changes to the source table and then sync it into the destination table.

Depending on your company/project you may have two or three environments.

Ideal Situation:

1. Make change(s) on Development table(s)
2. Test the change(s) and have someone else review the change(s)
3. Promote/Sync approved change(s) into Staging table(s)
4. Review the change(s) once again in the production-like environment
5. Promote/Sync approved/reviewed change(s) into Production table(s) in off hours
6. Review the change(s) once again in the Production environment

Provided there aren’t any problems along the way it should go smoothly through those steps.

Important: This depends on how your environment(s) work, in this example below it is assumed that the Development or Staging environments are newer than the Production environment

DECLARE @TransactionName VARCHAR(20) = 'SyncTableTransaction';

BEGIN TRAN @TransactionName

BEGIN TRY
	DELETE
	FROM Production.[tbl_zip_code];

	SET IDENTITY_INSERT Production.[tbl_zip_code] ON;

	INSERT INTO Production.[tbl_zip_code] (
		[id]
		,[zip_code]
		,[city]
		,[county]
		,[state_name]
		,[state_prefix]
		,[area_code]
		,[time_zone]
		,[lat]
		,[lon]
		)
	SELECT [id]
		,[zip_code]
		,[city]
		,[county]
		,[state_name]
		,[state_prefix]
		,[area_code]
		,[time_zone]
		,[lat]
		,[lon]
	FROM DevDB.[tbl_zip_code];

	SET IDENTITY_INSERT Production.[tbl_zip_code] OFF;
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_PROCEDURE() AS ErrorProcedure
		,ERROR_LINE() AS ErrorLine
		,ERROR_MESSAGE() AS ErrorMessage;

	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
	COMMIT TRANSACTION;
GO

If everything goes correctly you should be able to do the first query and get zero results.

Once you have adjusted the code above to suit your purpose it is suggested to create a stored procedure and determine if you need to schedule that procedure to run on a regular basis.

References:

• SQL Transactions
  • Basic Transaction
    • https://msdn.microsoft.com/en-us/library/ms188929.aspx
  • Rollback If Failure
    • https://msdn.microsoft.com/en-us/library/ms181299.aspx
• SQL Try Catch
  • https://msdn.microsoft.com/en-us/library/ms175976.aspx