Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.
Microsoft cloud engineer - SharePoint, Office 365, Azure, DotNet, Angular, JavaScript.

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! 

shades_smile

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

image
image
image
image

Reference

© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲