/* =====================================================
FULL REFRESH: TRUNCATE + RELOAD (COPY DATA AS-IS)
===================================================== */
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO
SET NOCOUNT ON;
/* ---------- CONFIGURE DATABASES ---------- */
DECLARE @SourceDB SYSNAME = 'HCHB_CANTEX';
DECLARE @TargetDB SYSNAME = 'HCHB_CTX';
/* ---------- TABLE LIST ---------- */
DECLARE @Tables TABLE (SchemaName SYSNAME, TableName SYSNAME);
INSERT INTO @Tables VALUES
('dbo','AGENCIES_SERVICELINES_BRANCHES'),
('dbo','AGENCY_RCD_SUSPENSION_PERIODS'),
('dbo','BRANCHES'),
('dbo','CLIENT_EPISODE_BENEFIT_PERIOD'),
('dbo','CLIENT_EPISODE_FACETOFACE'),
('dbo','CLIENT_EPISODE_FS'),
('dbo','CLIENT_EPISODE_FS_PPS_PRECLAIM_REVIEW'),
('dbo','CLIENT_EPISODE_FS_PPS_PRECLAIM_REVIEW_NONAFFIRMATION_REASONS'),
('dbo','Client_Episode_FS_PreClaim_Review_Answers'),
('dbo','CLIENT_EPISODES_ALL'),
('dbo','CLIENT_ORDERS_ALL'),
('dbo','FACETOFACE_REVIEW_STATUSES'),
('dbo','PAYOR_SOURCE_FACE_TO_FACE_SETUP'),
('dbo','PAYOR_SOURCE_HOME_HEALTH_SETTINGS'),
('dbo','PAYOR_SOURCES'),
('dbo','PAYOR_TYPES'),
('dbo','PPS_CLIENT_EPISODE_PAYOR_PRECLAIM_REVIEW_DATA'),
('dbo','PPS_PRECLAIM_REVIEW_COVERAGE_DETERMINATIONS'),
('dbo','PPS_PRECLAIM_REVIEW_STATES'),
('dbo','PPS_PRECLAIM_REVIEW_SUBMISSION_STATUSES'),
('dbo','Questions'),
('dbo','TEAMS'),
('PDGM','NOTICE_OF_ADMISSION'),
('PDGM','PDGM_PERIOD'),
('dbo','PHYSICIANS'),
('dbo','PHYSICIAN_OFFICES'),
('dbo','SNAPSHOT_RPTCLIENT_485'),
('dbo','CLIENT_EPISODE_VISITS_ALL'),
('dbo','WORKER_BASE'),
('dbo','WORKER_BASE_PAYROLL_DETAILS'),
('dbo','CLIENT_485_ALL'),
('dbo','CLIENT_EPISODE_SERVICE_LOCATIONS'),
('dbo','CLIENTS_ALL'),
('dbo','PAYOR_SOURCE_BRANCHES'),
('dbo','SYSTEM_SETTINGS'),
('dbo','REQUEST'),
('dbo','CLIENT_CALENDAR'),
('dbo','SERVICECODES'),
('dbo','JOB_DESCRIPTIONS'),
('dbo','SCHED'),
('dbo','TimeCards_Rounding_Rules'),
('dbo','TimeCards_Rounding_Templates'),
('dbo','TimeCards_Rounding_StoredProcs'),
('dbo','TimeCards_Rounding_Rules_Filters'),
('dbo','TimeCards_Rounding_Filters'),
('dbo','WORKER_JOBDESCRIPTIONS'),
('dbo','PROGRAMS'),
('dbo','DISCIPLINES'),
('dbo','BILLING_CODES'),
('dbo','PDGM_HCPCS'),
('dbo','SERVICES'),
('dbo','CLIENT_EPISODE_VISIT_CLAIM_CODES'),
('dbo','CLAIM_CODES'),
('dbo','CLIENT_EPISODE_FREQUENCY_WEEKS'),
('dbo','CLIENT_EPISODE_ORDERED_VISITS'),
('dbo','CLIENT_EPISODE_VISIT_NOTES'),
('dbo','CLIENT_EPISODE_VISIT_VS_ALERTS'),
('dbo','CLIENT_EPISODE_VISIT_VS_READINGS'),
('dbo','VITALSIGN_TYPES'),
('dbo','CLIENT_EPISODE_VITALSIGNPARAMETERS'),
('dbo','CLIENT_485_GOALS'),
('dbo','CLIENT_EPISODE_PHYSICIAN_PROTOCOLS'),
('dbo','PHYSICIAN_PROTOCOLS'),
('dbo','PATIENT_MEDS'),
('dbo','PATIENT_MED_ATTRIBUTES'),
('dbo','PATIENT_MEDS_UNDERSTANDING'),
('dbo','PATIENT_MEDS_TO_MEDS_UNDERSTANDING'),
('dbo','PATIENT_ORDER_MEDS'),
('dbo','CLIENT_MEDS'),
('dbo','CLIENT_WOUNDS'),
('dbo','CLIENT_WOUNDS_ORDERS'),
('dbo','CLIENT_WOUND_HISTORY');
/* ---------- VARIABLES ---------- */
DECLARE
@Schema SYSNAME,
@Table SYSNAME,
@Cols NVARCHAR(MAX),
@SQL NVARCHAR(MAX),
@HasIdentity BIT;
/* ---------- CURSOR ---------- */
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT SchemaName, TableName FROM @Tables;
OPEN cur;
FETCH NEXT FROM cur INTO @Schema, @Table;
WHILE @@FETCH_STATUS = 0
BEGIN
/* ---------- Build column list + identity detection ---------- */
SET @SQL = N'
SELECT
@ColsOut =
STUFF((
SELECT '','' + QUOTENAME(c.name)
FROM ' + QUOTENAME(@TargetDB) + '.sys.columns c
WHERE c.object_id = OBJECT_ID(''' + @TargetDB + '.' + @Schema + '.' + @Table + ''')
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''''), TYPE
).value(''.'',''nvarchar(max)''),1,1,''''),
@HasIdentityOut =
CASE
WHEN EXISTS (
SELECT 1
FROM ' + QUOTENAME(@TargetDB) + '.sys.columns
WHERE object_id = OBJECT_ID(''' + @TargetDB + '.' + @Schema + '.' + @Table + ''')
AND is_identity = 1
)
THEN 1 ELSE 0
END;
';
EXEC sys.sp_executesql
@SQL,
N'@ColsOut NVARCHAR(MAX) OUTPUT, @HasIdentityOut BIT OUTPUT',
@ColsOut = @Cols OUTPUT,
@HasIdentityOut = @HasIdentity OUTPUT;
IF @Cols IS NULL OR LEN(@Cols) = 0
BEGIN
PRINT 'Skipping ' + @Schema + '.' + @Table + ' (no usable columns)';
FETCH NEXT FROM cur INTO @Schema, @Table;
CONTINUE;
END
/* ---------- TRUNCATE ---------- */
SET @SQL = N'
PRINT ''Truncating ' + @Schema + '.' + @Table + ''';
TRUNCATE TABLE ' + QUOTENAME(@TargetDB) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ';
';
EXEC sys.sp_executesql @SQL;
/* ---------- INSERT (IDENTITY SAFE – SINGLE BATCH) ---------- */
SET @SQL = N'
PRINT ''Reloading ' + @Schema + '.' + @Table + ''';
' + CASE WHEN @HasIdentity = 1 THEN
'SET IDENTITY_INSERT ' + QUOTENAME(@TargetDB) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' ON;'
ELSE '' END + '
INSERT INTO ' + QUOTENAME(@TargetDB) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + '
(' + @Cols + ')
SELECT ' + @Cols + '
FROM ' + QUOTENAME(@SourceDB) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ';
' + CASE WHEN @HasIdentity = 1 THEN
'SET IDENTITY_INSERT ' + QUOTENAME(@TargetDB) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' OFF;'
ELSE '' END + '
';
EXEC sys.sp_executesql @SQL;
FETCH NEXT FROM cur INTO @Schema, @Table;
END
CLOSE cur;
DEALLOCATE cur;
PRINT '===== FULL REFRESH COMPLETED SUCCESSFULLY (DATA COPIED AS-IS) =====';
