--- title: "Rename DataSet/Hole/Sample" canonical: "https://servicedesk.maxgeo.com/space/MDS/1850212355/Rename%20DataSet%2FHole%2FSample" format: markdown --- *All content in this document is Copyright and all rights reserved,* *content should not be copied, adapted, redistributed, or otherwise* *without the prior permission of maxgeo Pty Ltd* > Macro (toc) # Version History | | | | | | --- | --- | --- | --- | | Version | Date | Person | **File Name** | | 1 | Jul 2024 | J. Liu | SOP_Rename_DataSet_Hole_Sample_Guide.docx | # Executive Summary From time to time, we might need to rename DataSet, Hole or Sample or the combination of them. Due to data dependencies, it might be a challenge to perform the task. This guide discusses possible methods for renaming. This guide discusses method to perform renaming DataSet/Hole/Sample. Depending on the actual renaming requirements, it might be a challenge to perform the renaming task due to data dependencies. This guide discusses two methods for renaming. # 1.0  Purpose of this Guide This guide discusses method to perform renaming DataSet/Hole/Sample. Depending on the actual renaming requirements, it might be a challenge to perform the renaming task due to data dependencies. This guide discusses two methods for renaming. # 2.0 Renaming Methods ### 2.1. Method 1: Direct update **This method is suitable if need to rename entire DataSet or Hole. It depends on the update cascade setting on the foreign key on child tables. If FK update cascade option is not on or no FK to parent table, then this method will either fail (for FK update cascade off) or no update on child table without FK (and need manually update those child tables).** If need to rename entire DataSet, simply run an update statement against tblSYSDataSet. If update failed due to FK issue or want to handle tables without FK to tblSYSDataSet, then we will need to use Method2. If need to rename entire Hole, simply run an update statement against tblDHColl. If update failed due to FK issue or want to handle tables without FK to tblDHColl, then we will need to use Method2. ### 2.2. Method 2: usp_RenameDataSetHoleSample **Disclaimer: The SP is provided AS IS. Please test the renaming in a test database and only implement in production database once satisfied with the results. Please ensure a good database backup exists. Depends on the issue with renaming process encountered, you may need to restore the database from the backup.** This method is suitable when Method1 failed or need to do partial DataSet/Hole renaming or only rename samples. Install the SP to your database and then call the SP with appropriate parameters. Following are the explanation of each parameter: **@Orig_DataSet:** The current DataSet value. This is required in any renaming scenario. **@Orig_Hole_ID:** The current Hole_ID value. Optional. **@Orig_SampleID:** The current SampleID value. Optional. **@New_DataSet:** The new DataSet value. Optional. **@New_Hole_ID:** The new Hole_ID value. Optional. **@New_SampleID:** The new SampleID value. Optional. **@AssayTables:** The list of assay (including flat) tables in your MDS, use comma to separate. This is required if @New_SampleID is specified. At minimum, needs to specify tblAssay,tblAssayFlat. **@DHSampleTables:** This list of down hole samples (include QC tables) in your MDS, use comma to separate. This is required if @Orig_Hole_ID is specified. At minimum, needs to specify tblDHSamp,tblDHSampQC. **@StopOnBatchWithMultiHoles:** Flag indicates rename process to stop when detect batch involved contains multiple holes. Default value is 1 (True). This flag is applicable when rename hole. The Hole_ID (Source_ID) for standard sample is determined based on the hole in the batch. When a batch involves multiple holes, the SP can’t determine which hole to be assigned to the standard sample. When this flag set to 1 (Ture), renaming process will stop and all changes roll back when multiple holes in a batch is detected. When this flag set to 0 (False), renaming process will ignore update the Hole_ID (Source_ID) for the standard sample and continue. **@Report:** This flag indicates either list the tables involved in the renaming or not. Optional. Default is 1 (True). **@TablesToExclude:** The list of tables to be excluded from renaming process, using comma to separate. Optional. **@CaptureStandardSampErrorTo:** Table name to be used to capture any standard sample that can not be updated due to batch has multiple holes. The renaming process will create a table, if not already exists, using this name. The standard sample in question will be added into this table for review. This is helpful when @StopOnBatchWithMultiHoles is set to 0. **@Debug:** This parameter is for debugging. Optional. Default is 0. **@RebuidIndex:** This flag indicates if index will be rebuilt or not. Optional. Default is 0. This flag might be helpful only when write advanced query to do bulk renaming. **@ReEnableConstraint:** This flag indicates will constraint be reenabled or not. Optional. Default is 1 (reenable). This flag might be helpful only when write advanced query to do bulk renaming.   Only one rename action, either rename dataset or hole or sample, can be performed at a given time. So, @New_DataSet, @New_Hole_ID and @New_SampleID can only have one with value specified. If needs to rename multiple part on the same record, split them into multiple rename queries.   Renaming is single process on the combination of @Orig_DataSet/@Orig_Hole_ID/@Orig_SampleID specified. You can write additional script to loop through your batch items.   When @Orig_Hole_ID is specified, tables cannot be related to tblDHSamp will not be processed, except for tblSampleRegister, tblAssay, tblAssayFlat.   When renaming SampleID, Orig_SampleID and Historic_SampleID will also be updated.   When renaming hole, only batch for tblAssay.Preferred=0 records are renamed on tblStandardSamp/QC tables.   When process renaming in batch mode with Hole_ID involved, if there is more than one DH sample table involved, please CREATE TABLE #usp_RenameDataSetHoleSample_STDSampFilter (DataSet NVARCHAR(30) COLLATE DATABASE_DEFAULT, Hole_ID NVARCHAR(100) COLLATE DATABASE_DEFAULT, Lab_Code NVARCHAR(20) COLLATE DATABASE_DEFAULT, Batch_No NVARCHAR(25) COLLATE DATABASE_DEFAULT, TableID INT, NewDataSet NVARCHAR(30) COLLATE DATABASE_DEFAULT) in your batch script and the SP will use that temp table to boost the renaming process. This temp table needs to be created outside the SP loop in the batch script.   When renaming samples in batch mode, please CREATE TABLE #usp_RenameDataSetHoleSample_SampleBatch (DataSet NVARCHAR(30) COLLATE DATABASE_DEFAULT, SampleID NVARCHAR(50) COLLATE DATABASE_DEFAULT, New_SampleID NVARCHAR(50) COLLATE DATABASE_DEFAULT) and populate it with the samples in question and then simply call the SP with all default parameter values.   When renaming DataSet, system tables, except tblSYSDataSet, are excluded from the process. For performance reason, when renaming in batch mode, create this temp table #usp_RenameDataSetHoleSample_ReEnableConstraints (Definition NVARCHAR(MAX)) outside this SP and set @ReEnableConstraint = 0 and handle reEnableConstraint outside this SP.   When renaming in batch mode, for standard sample reported couldn’t be processed in the earlier stage due to the batch involves multiple holes, these standard sample might be processed with the last hole after other holes have been renamed.