VIDEO – Sync tables with easy TSQL Stored Procedure

I needed to merge two tables of data by matching primary key and then populating all other columns.   The below TSQL code will accomplish that quickly and easily native within SQL.   No need for remote client side PowerShell Dot Net.   Hope you find this helpful.  Cheers! 

Video

Code

CREATE PROCEDURE [dbo].[Sync] AS 
--Insert new rows
PRINT 'INSERT'
INSERT INTO [States]
([Name])
SELECT 
[Name]
FROM [States-Temp]
WHERE [Name] NOT IN (SELECT [Name] FROM [States])
--Delete excess rows
PRINT 'DELETE'
DELETE FROM [States] 
WHERE [Name] NOT IN (SELECT [Name] FROM [States-Temp])
--Update matching rows
PRINT 'UPDATE'
UPDATE t2 
SET t2.Abbr = t1.Abbr,
t2.Population = t1.Population,
t2.Capital = t1.Capital
FROM [States] AS t2
INNER JOIN [States-Temp] AS t1 
ON t2.[Name]=t1.[Name]
GO

Screenshots

Reference

• https://stackoverflow.com/questions/18588304/sync-two-tables-one-way-in-the-same-database-using-stored-procedure
• a href=”https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States”>https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States