How do i merge two or more rows based on their foreign key?
This is the query that merges two rows based on a foreign key (breaks the rule of 1NF).
DECLARE @StrengthID INT
DECLARE @GenericDrugDetailsID INT
DEClARE @DosageStrength INT
DECLARE @PresentationUnitID INT
DECLARE @DosageFormID INT
DECLARE @RouteOfAdministrationID INT
DECLARE @nCombinations INT
DECLARE @maxCombinations INT
DECLARE @CurrCombinationTotal INT
DECLARE @CurrGenericDrugDetailsID INT
DECLARE @PName VARCHAR(100)
DECLARE @DName VARCHAR(100)
DECLARE @DNameAbbrev VARCHAR(50)
DECLARE @RName VARCHAR(100)
DECLARE @GetGenericDrugStrengths CURSOR
SET @nCombinations = 0
SET @CurrGenericDrugDetailsID = 0
--Get the maximum number of combinations
--=====================================================================================
SET @maxCombinations = (SELECT TOP 1 COUNT(GenericDrugDetailsID) AS maxCombinations
FROM Pharmacy_Strengths
GROUP BY GenericDrugDetailsID
ORDER BY maxCombinations desc)
--=====================================================================================
--Create a temporary temp to hold the strengths
--=====================================================================================
DECLARE @tmpSQL VARCHAR(max)
SET @tmpSQL = ''
IF EXISTS (SELECT * FROM tempdb.sys.tables WHERE NAME LIKE '%tmpStrengths%')
DROP TABLE #tmpStrengths
CREATE TABLE #tmpStrengths (
StrengthID INT NOT NULL,
GenericDrugDetailsID INT NOT NULL,
NumberOfCombinations INT NOT NULL,
DosageStrength1 INT NOT NULL,
PresentationUnitID1 INT NOT NULL,
PresentationUnitName1 VARCHAR(100) NOT NULL,
DosageFormID1 INT NOT NULL,
DosageFormName1 VARCHAR(100) NOT NULL,
DosageFormNameAbbrev1 VARCHAR(50) NULL,
RouteOfAdministrationID1 INT NOT NULL,
RouteOfAdministrationName1 VARCHAR(100) NOT NULL);
IF (@maxCombinations > 1)
BEGIN
DECLARE @counter int
SET @counter = 1
WHILE @counter < @maxCombinations
BEGIN
SET @counter = @counter + 1
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD StrengthID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL;'
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageStrength' + CAST(@counter AS VARCHAR(50)) + ' INT NULL;'
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD PresentationUnitID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL; '
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD PresentationUnitName' + CAST(@counter AS VARCHAR(100)) + ' VARCHAR(100) NULL; '
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageFormID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL; '
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageFormName' + CAST(@counter AS VARCHAR(100)) + ' VARCHAR(100) NULL; '
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD DosageFormNameAbbrev' + CAST(@counter AS VARCHAR(50)) + ' VARCHAR(50) NULL; '
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD RouteOfAdministrationID' + CAST(@counter AS VARCHAR(50)) + ' INT NULL; '
SET @tmpSQL = @tmpSQL + 'ALTER TABLE #tmpStrengths ADD RouteOfAdministrationName' + CAST(@counter AS VARCHAR(100)) + ' VARCHAR(100) NULL; '
END
END
EXEC(@tmpSQL)
--====================================================================================
SET @tmpSQL = ''
SET @GetGenericDrugStrengths = CURSOR FOR
SELECT StrengthID, GenericDrugDetailsID, DosageStrength, PresentationUnitID, DosageFormID, RouteOfAdministrationID
FROM Pharmacy_Strengths
ORDER BY GenericDrugDetailsID ASC
OPEN @GetGenericDrugStrengths
FETCH NEXT FROM @GetGenericDrugStrengths INTO @StrengthID, @GenericDrugDetailsID, @DosageStrength, @PresentationUnitID, @DosageFormID, @RouteOfAdministrationID
WHILE @@FETCH_STATUS = 0
BEGIN
--Get the values of Presentation Unit, Dosage Form, and Route of Administration.
SELECT @PName = PresentationUnitName
FROM Pharmacy_PresentationUnit
WHERE PresentationUnitID = @PresentationUnitID
SELECT @DName = DosageFormName, @DNameAbbrev = DosageFormNameAbbrev
FROM Pharmacy_DosageForm
WHERE DosageFormID = @DosageFormID
SELECT @RName = RouteOfAdministrationName
FROM Pharmacy_RouteOfAdministration
WHERE RouteOfAdministrationID = @RouteOfAdministrationID
IF (@GenericDrugDetailsID = @CurrGenericDrugDetailsID)
BEGIN
SET @nCombinations = (@nCombinations + 1)
--Alter the temporary table now
SET @tmpSQL = @tmpSQL + 'UPDATE #tmpStrengths
SET StrengthID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@StrengthID AS VARCHAR(50)) + ',
DosageStrength' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@DosageStrength AS VARCHAR(50)) + ',
PresentationUnitID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@PresentationUnitID AS VARCHAR(50)) + ',
PresentationUnitName' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@PName AS VARCHAR(100)) + ''',
DosageFormID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@DosageFormID AS VARCHAR(50)) + ',
DosageFormName' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@DName AS VARCHAR(100)) + ''',
DosageFormNameAbbrev' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@DNameAbbrev AS VARCHAR(50)) + ''',
RouteOfAdministrationID' + CAST(@nCombinations AS VARCHAR(50)) + ' = ' + CAST(@RouteOfAdministrationID AS VARCHAR(50)) + ',
RouteOfAdministrationName' + CAST(@nCombinations AS VARCHAR(50)) + ' = ''' + CAST(@RName AS VARCHAR(100)) + '''
WHERE GenericDrugDetailsID = ' + CAST(@GenericDrugDetailsID AS VARCHAR(50));
EXEC(@tmpSQL);
END
ELSE
BEGIN
SET @nCombinations = 1
SET @CurrGenericDrugDetailsID = @GenericDrugDetailsID;
--Get the total number of combinations in advance
SET @CurrCombinationTotal = (SELECT COUNT(GenericDrugDetailsID)
FROM Pharmacy_Strengths
WHERE GenericDrugDetailsID = @GenericDrugDetailsID
GROUP BY GenericDrugDetailsID);
--Insert in the temporary table now
INSERT INTO #tmpStrengths (StrengthID, GenericDrugDetailsID, NumberOfCombinations,
DosageStrength1, PresentationUnitID1, PresentationUnitName1, DosageFormID1,
DosageFormName1, DosageFormNameAbbrev1, RouteOfAdministrationID1, RouteOfAdministrationName1)
VALUES (@StrengthID, @GenericDrugDetailsID, @CurrCombinationTotal, @DosageStrength,
@PresentationUnitID, @PName, @DosageFormID, @DName, @DNameAbbrev, @RouteOfAdministrationID, @RName)
END
--PRINT CAST(@StrengthID AS varchar(50)) + ' ' + CAST(@GenericDrugDetailsID AS varchar(50)) + ' ' + CAST(@DosageStrength AS varchar(50)) + ' ' + CAST(@PresentationUnitID AS varchar(50)) + ' ' + CAST(@DosageFormID as varchar(50)) + ' ' + CAST(@RouteOfAdministrationID as varchar(50))
FETCH NEXT FROM @GetGenericDrugStrengths INTO @StrengthID, @GenericDrugDetailsID, @DosageStrength, @PresentationUnitID, @DosageFormID, @RouteOfAdministrationID
END
--SELECT * from #tmpStrengths
CLOSE @GetGenericDrugStrengths
DEALLOCATE @GetGenericDrugStrengths
SELECT a.GenericDrugID,
a.GenericDrugName,
f.*,
a.InsertDate,
a.InsertFKUserAccountId,
a.UpdateDate,
a.UpdateFKUserAccountId,
a.Version
FROM Pharmacy_GenericDrug a
INNER JOIN Pharmacy_ControlStatus d ON d.ControlStatusID = a.ControlStatusID
INNER JOIN Pharmacy_GenericDrugDetails e ON e.GenericDrugID = a.GenericDrugID
INNER JOIN #tmpStrengths f ON f.GenericDrugDetailsID = e.GenericDrugDetailsID
ORDER BY GenericDrugName ASC
This is what i get before the query:
GenericDrugID GenericDrugName DrugSubClassificationID DrugSubClassificationName DrugClassificationID DrugClassificationName GenericDrugDetailsID DosageStrength PresentationUnitID PresentationUnitName DosageFormID DosageFormName DosageFormNameAbbrev RouteOfAdministrationID RouteOfAdministrationName ControlStatusID ControlStatusName InsertDate InsertFKUserAccountId UpdateDate UpdateFKUserAccountId Version
------------------------------------ ---------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------- ------------------ ---------------------------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------------------------------------------- -------------------------------------------------- ----------------------- ---------------------------------------------------------------------------------------------------- --------------- ---------------------------------------------------------------------------------------------------- ----------------------- --------------------- ----------------------- --------------------- -----------------------
83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 2 300 1 ml 2 Pill Pill 1 Mouth 3 Narcotic 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.077
83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 2 400 1 ml 2 Pill Pill 1 Mouth 3 Narcotic 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.077
83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 4 500 1 ml 2 Pill Pill 2 Injection 3 Narcotic 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.077
A1D86FD2-2E65-4F2A-B7A2-7B083B71AB9B Generic Drug Two 9925D762-34B8-43B4-A96B-78374F0081B0 Drug Subclassification One C10E9F1B-3985-4DB8-8EF8-A6C82B004B9F Drug Classification Two 1 200 1 ml 2 Pill Pill 1 Mouth 2 None 2009-10-25 10:47:10.623 1 2009-11-15 04:02:47.890 1 2009-10-25 10:47:10.623
This is what i get now:
GenericDrugID GenericDrugName DrugSubClassificationID DrugSubClassificationName DrugClassificationID DrugClassificationName StrengthID GenericDrugDetailsID NumberOfCombinations DosageStrength1 PresentationUnitID1 PresentationUnitName1 DosageFormID1 DosageFormName1 DosageFormNameAbbrev1 RouteOfAdministrationID1 RouteOfAdministrationName1 StrengthID2 DosageStrength2 PresentationUnitID2 PresentationUnitName2 DosageFormID2 DosageFormName2 DosageFormNameAbbrev2 RouteOfAdministrationID2 RouteOfAdministrationName2 InsertDate InsertFKUserAccountId UpdateDate UpdateFKUserAccountId Version
------------------------------------ ---------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- -------------------- --------------- ------------------- ---------------------------------------------------------------------------------------------------- ------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------------------ ---------------------------------------------------------------------------------------------------- ----------- --------------- ------------------- ---------------------------------------------------------------------------------------------------- ------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- ------------------------ ---------------------------------------------------------------------------------------------------- ----------------------- --------------------- ----------------------- --------------------- -----------------------
83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 2 2 2 300 1 ml 2 Pill Pill 1 Mouth 3 400 1 ml 2 Pill Pill 1 Mouth 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.077
83222B76-6690-42F2-BDAD-BE5BD2D19D3B Generic Drug One 8ED135ED-5FDB-419A-9C7D-E788A40EAEAC Drug Subclassification Two 61D618F3-A38A-4416-B0A4-FB29AD614B4B Drug Classification One 4 4 1 500 1 ml 2 Pill Pill 2 Injection NULL NULL NULL NULL NULL NULL NULL NULL NULL 2009-10-25 10:10:06.077 1 2009-11-15 07:06:03.733 1 2009-10-25 10:10:06.077
A1D86FD2-2E65-4F2A-B7A2-7B083B71AB9B Generic Drug Two 9925D762-34B8-43B4-A96B-78374F0081B0 Drug Subclassification One C10E9F1B-3985-4DB8-8EF8-A6C82B004B9F Drug Classification Two 1 1 1 200 1 ml 2 Pill Pill 1 Mouth NULL NULL NULL NULL NULL NULL NULL NULL NULL 2009-10-25 10:47:10.623 1 2009-11-15 04:02:47.890 1 2009-10-25 10:47:10.623
For those interested these are the table structures:
CREATE TABLE [dbo].[Pharmacy_GenericDrug](
[GenericDrugID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Pharmacy___Gener__4E53A1AA] DEFAULT (newid()),
[GenericDrugName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DrugSubClassificationID] [uniqueidentifier] NOT NULL,
[ControlStatusID] [int] NOT NULL,
[InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_InsertDate] DEFAULT (getdate()),
[InsertFKUserAccountId] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_UpdateDate] DEFAULT (getdate()),
[UpdateFKUserAccountId] [int] NOT NULL,
[Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrug_Version] DEFAULT (getdate())
)
CREATE TABLE [dbo].[Pharmacy_GenericDrugDetails](
[GenericDrugDetailsID] [int] IDENTITY(1,1) NOT NULL,
[GenericDrugID] [uniqueidentifier] NOT NULL,
[InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_InsertDate] DEFAULT (getdate()),
[InsertFKUserAccountId] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_UpdateDate] DEFAULT (getdate()),
[UpdateFKUserAccountId] [int] NOT NULL,
[Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_GenericDrugDetails_Version] DEFAULT (getdate())
)
CREATE TABLE [dbo].[Pharmacy_Strengths](
[StrengthID] [int] IDENTITY(1,1) NOT NULL,
[GenericDrugDetailsID] [int] NOT NULL,
[DosageStrength] [int] NOT NULL,
[PresentationUnitID] [int] NOT NULL,
[DosageFormID] [int] NOT NULL,
[RouteOfAdministrationID] [int] NOT NULL,
[InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_InsertDate] DEFAULT (getdate()),
[InsertFKUserAccountId] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_UpdateDate] DEFAULT (getdate()),
[UpdateFKUserAccountId] [int] NOT NULL,
[Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_StrengthCombination_Version] DEFAULT (getdate())
)
CREATE TABLE [dbo].[Pharmacy_PresentationUnit](
[PresentationUnitID] [int] IDENTITY(1,1) NOT NULL,
[PresentationUnitName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_InsertDate] DEFAULT (getdate()),
[InsertFKUserAccountId] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_UpdateDate] DEFAULT (getdate()),
[UpdateFKUserAccountId] [int] NOT NULL,
[Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_PresentationUnit_Version] DEFAULT (getdate())
)
CREATE TABLE [dbo].[Pharmacy_DosageForm](
[DosageFormID] [int] IDENTITY(1,1) NOT NULL,
[DosageFormName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DosageFormNameAbbrev] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_InsertDate] DEFAULT (getdate()),
[InsertFKUserAccountId] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_UpdateDate] DEFAULT (getdate()),
[UpdateFKUserAccountId] [int] NOT NULL,
[Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_DosageForm_Version] DEFAULT (getdate())
)
CREATE TABLE [dbo].[Pharmacy_RouteOfAdministration](
[RouteOfAdministrationID] [int] IDENTITY(1,1) NOT NULL,
[RouteOfAdministrationName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InsertDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_InsertDate] DEFAULT (getdate()),
[InsertFKUserAccountId] [int] NOT NULL,
[UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_UpdateDate] DEFAULT (getdate()),
[UpdateFKUserAccountId] [int] NOT NULL,
[Version] [datetime] NOT NULL CONSTRAINT [DF_Pharmacy_RouteOfAdministration_Version] DEFAULT (getdate())
)
Merge multiple records for the same foreign key
You can create dynamic sql using sys.columns system view as follows
CREATE TABLE Table1
([ID] int, [ReportID] int, [FieldID] int, [Col1] varchar(3), [Col2] int, [Col3] varchar(3));
INSERT INTO Table1
([ID], [ReportID], [FieldID], [Col1], [Col2], [Col3])
VALUES
(1, 2, 1, 'abc', NULL, NULL),
(2, 2, 2, NULL, 123, NULL),
(3, 2, 3, NULL, NULL, 'A3A'),
(4, 3, 1, NULL, NULL, NULL),
(5, 3, 2, NULL, 456, NULL),
(6, 3, 3, 'def', NULL, NULL);
declare @sql varchar(max)='select MIN(ID) ID, ReportID, MIN(FieldID) FieldID '
select @sql+=',MIN('+c.name+') '+c.name from sys.columns c
where object_id=object_id(N'table1')
and c.name not in ('ID','ReportID','FieldID')
select @sql+=' from table1 group by ReportID'
exec (@sql)
drop table Table1
But in this case you may need to grant select permission on sys.columns in master database.
I think, it is better to group your data in the query where the table is created.
How to merge rows of one table to another while keeping foreign key constraints on autogenerated columns?
You can try something like the following. In here we assume that you need to insert all records as new ones (and not compare if some already exist or not). I wrapped both operations in a transaction to ensure that both go OK or none at all.
BEGIN TRY
IF OBJECT_ID('tempdb..#IdentityRelationships') IS NOT NULL
DROP TABLE #IdentityRelationships
CREATE TABLE #IdentityRelationships (
OldIdentity INT,
NewIdentity INT)
BEGIN TRANSACTION
;WITH SourceData AS
(
SELECT
OldIdentity = A.Id_A,
OtherColumn = A.OtherColumn
FROM
Database1.Schema.TableA AS A
)
MERGE INTO
Database2.Schema.TableA AS T
USING
SourceData AS S ON 1 = 0 -- Will always execute the "WHEN NOT MATCHED" operation
WHEN NOT MATCHED THEN
INSERT (
OtherColumn)
VALUES (
S.OtherColumn)
OUTPUT
inserted.Id_A, -- "MERGE" clause can output non-inserted values
S.ID_A
INTO
#IdentityRelationships (
NewIdentity,
OldIdentity);
INSERT INTO Database2.Schema.TableB (
RefId_A,
OtherData)
SELECT
RefId_A = I.NewIdentity,
OtherData = T.OtherData
FROM
Database1.Schema.TableB AS T
INNER JOIN #IdentityRelationships AS I ON T.RefID_A = I.OldIdentity
COMMIT
END TRY
BEGIN CATCH
DECLARE @v_ErrorMessage VARCHAR(MAX) = CONVERT(VARCHAR(MAX), ERROR_MESSAGE())
IF @@TRANCOUNT > 0
ROLLBACK
RAISERROR (@v_ErrorMessage, 16, 1)
END CATCH
Related Topics
Hibernate Create Criteria to Join the Same Table Twice - Tried 2 Approach with 2 Difference Error
Slick 3.0 - Update Columns in a Table and Return Whole Table Object
Create a Table of Two Types in Postgresql
How to Sum Multiple Lines in SQL
Continuing Inserts in Oracle When Exception Is Raised
What Does It Mean to Have Jobs with a Null Stop Date
Unique Constraint Controlled by a Bit Column
Sqlserver Case Expressions - Short Circuit Evaluation
How to Use SQL Server Compact Edition Ce on Mono
Sqlite Equivalent of Postgresql's Greatest Function
Update Table with Random Record in Update Statment in SQL Server