>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:

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