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
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
With custom SQL line of business databases it can be helpful to enumerate all tables and row counts for each. The below TSQL will create a new VIEW named “RowCounts” for that purpose. I’d suggest adding this to any custom SQL database for easy data quality checks and monitoring.
Cheers!
TSQL RowCount
CREATE VIEW RowCounts AS (
SELECT o.NAME,
i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o
ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0)
TSQL Storage
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
Use the below TSQL script to set all databases to simple recovery mode. Helpful for development environments and lab VMs to save on disk storage and backup space.
Cheers!
Code
USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql='USE @dbname; DBCC SHRINKFILE (N''@dbname_log'' , 0, TRUNCATEONLY)'
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1