>>Hi,
>>
>>I am considering a feature for my app that would allow user combining two records into one. For example, say in the parts table they have two pars "1234" and "1234A" and at some point (after years of use) they realize that this is one and the same part. They can't rename one into the other because another record of unique entry already exists. They can't delete one because it has child records (transactions, P.O.s etc.). So I am thinking of writing a routine that would do it in code. Does it make sense? What I mean is, do others (as other developers on this message board) have routines that allow combining two records with different values in unique field into one? TIA.
>
>Yes, it does make sense. It's called de-duping (one p) process. And the code in it depends on your tables structures.
>
>Say, this is the procedure I use to delete info when it has lots of related info - in your case you will have to re-map info which may be even a more complex process:
>

Thank you for the sample code. I will look at it closer to see what I can "borrow". As to the term, I will use the term "merge" rather than de-duping just so that I don't offend a customer <g>.

>

>ALTER PROCEDURE dbo.siriussp_rsDeleteVenue (@VenueID INT)
>AS
>BEGIN
>	SET NOCOUNT ON;
>
>	BEGIN TRY
>		IF OBJECT_ID('TempDB..#DelTemplates', 'U') IS NOT NULL
>			DROP TABLE #DelTemplates;
>
>		IF OBJECT_ID('TempDB..#DelLevels', 'U') IS NOT NULL
>			DROP TABLE #DelLevels;
>
>		IF OBJECT_ID('TempDB..#DelSections', 'U') IS NOT NULL
>			DROP TABLE #DelSections;
>
>		IF OBJECT_ID('TempDB..#DelBlocks', 'U') IS NOT NULL
>			DROP TABLE #DelBlocks;
>
>		IF OBJECT_ID('TempDB..#DelProds', 'U') IS NOT NULL
>			DROP TABLE #DelProds;
>
>		SELECT TemplateId
>		INTO #DelTemplates
>		FROM dbo.rsTemplates
>		WHERE VenueID = @VenueID;
>
>		SELECT ProdId
>		INTO #DelProds
>		FROM dbo.rsProds P
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelTemplates T
>				WHERE T.TemplateID = P.TemplateID
>				);
>
>		SELECT LevelID
>		INTO #DelLevels
>		FROM dbo.rsLevels L
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelTemplates D
>				WHERE D.TemplateID = L.TemplateID
>				);
>
>		SELECT SectionID
>		INTO #DelSections
>		FROM dbo.rsSections S
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelLevels D
>				WHERE D.LevelID = S.LevelID
>				);
>
>		SELECT BlockId
>		INTO #DelBlocks
>		FROM dbo.rsTSBlocks B
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelSections D
>				WHERE D.SectionID = B.SectionID
>				);
>
>		BEGIN TRANSACTION
>
>		-- Seat Maps
>		DELETE M
>		FROM dbo.rsSeatMaps M
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelTemplates D
>				WHERE D.TemplateID = M.TemplateID
>				);
>
>		-- Exceptions first
>		DELETE TE
>		FROM dbo.rsTExcepts TE
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelBlocks D
>				WHERE D.BlockID = TE.BlockID
>				);
>
>		-- Blocks
>		DELETE B
>		FROM dbo.rsTSBlocks B
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelBlocks D
>				WHERE D.BlockID = B.BlockID
>				);
>
>		-- Sections
>		DELETE S
>		FROM dbo.rsSections S
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelSections D
>				WHERE D.SectionID = S.SectionID
>				);
>
>		-- Levels 
>		DELETE L
>		FROM dbo.rsLevels L
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelLevels D
>				WHERE D.LevelID = L.LevelID
>				);
>
>		-- Shows and Productions
>		DELETE Sh
>		FROM dbo.rsShows Sh
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelProds P
>				WHERE P.ProdID = Sh.ProdID
>				);
>
>		DELETE P
>		FROM dbo.rsProds P
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelProds DP
>				WHERE P.ProdID = DP.ProdID
>				);
>
>		-- Template
>		DELETE T
>		FROM dbo.rsTemplates T
>		WHERE EXISTS (
>				SELECT 1
>				FROM #DelTemplates D
>				WHERE D.TemplateID = T.TemplateID
>				);
>
>		-- Venue
>		DELETE
>		FROM dbo.rsVenues
>		WHERE VenueID = @VenueID;
>
>		COMMIT TRANSACTION
>	END TRY
>
>	BEGIN CATCH
>		DECLARE @ErrorSeverity INT
>			,@ErrorNumber INT
>			,@ErrorMessage NVARCHAR(4000)
>			,@ErrorState INT
>			,@ErrorLine INT
>			,@ErrorProc NVARCHAR(200)
>
>		-- Grab error information from SQL functions
>		SET @ErrorSeverity = ERROR_SEVERITY()
>		SET @ErrorNumber = ERROR_NUMBER()
>		SET @ErrorMessage = ERROR_MESSAGE()
>		SET @ErrorState = ERROR_STATE()
>		SET @ErrorLine = ERROR_LINE()
>		SET @ErrorProc = ERROR_PROCEDURE()
>		SET @ErrorMessage = 'Problem deleting venue ' + CAST(@VenueID AS VARCHAR(10)) + CHAR(13) + 'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) + ' in procedure: ' + @ErrorProc + ' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) + ' Error text: ' + @ErrorMessage
>
>		-- Not all errors generate an error state, to set to 1 if it's zero
>		IF @ErrorState = 0
>			SET @ErrorState = 1
>
>		-- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback
>		IF @@TRANCOUNT > 0
>		BEGIN
>			ROLLBACK TRANSACTION
>		END
>
>		RAISERROR (
>				@ErrorMessage
>				,@ErrorSeverity
>				,@ErrorState
>				,@ErrorNumber
>				)
>	END CATCH
>
>	RETURN @@ERROR
>END
>GO
>