Test post

 /* =====================================================

   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) =====';