My post is bit long. Please bear with me. Thanks.
I have requirement where I need to transfer data from one database to another between the tables.
I have the database named ‘Child’ having following tables with schema below:
CREATE TABLE [dbo].[FreeTextDataDraft](
[FreeTextID] [bigint] IDENTITY(1,1) NOT NULL,
[NodeDataID] [bigint] NULL,
[TextData] [nvarchar](max) NULL,
CONSTRAINT [PK_FreeTextDataDraft] PRIMARY KEY CLUSTERED
(
[FreeTextID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[NodeAnswersDraft](
[NodeDataID] [bigint] IDENTITY(1,1) NOT NULL,
[FWID] [bigint] NULL,
[MemberID] [bigint] NULL,
[NodeID] [bigint] NULL,
[QuaterID] [tinyint] NULL,
[MaturityLevelID] [bigint] NULL,
[CurrentStatsID] [bigint] NULL,
[ActionPlanID] [bigint] NULL,
[CommentID] [bigint] NULL,
[EvidenceCommentID] [bigint] NULL
CONSTRAINT [PK_NodeAnswersDraft] PRIMARY KEY CLUSTERED
(
[NodeDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[FreeTextDataDraft] ON
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (1, 19, N'Current Status Testing_Phase2_9th Jan 2020_Test6 ')
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (2, 19, N'Action Plan Testing_Phase2_9th Jan 2020_Test5')
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (3, 19, N'Comments Testing_Phase2_9th Jan 2020_Test7')
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (4, 19, N'Evidence Comment Testing_Phase2_9th Jan 2020_Test8')
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (5, 20, N'Current Status Testing')
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (6, 20, N'Action Plan Testing')
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (7, 20, N'Comments Testing')
GO
INSERT [dbo].[FreeTextDataDraft] ([FreeTextID], [NodeDataID], [TextData]) VALUES (8, 20, N'Evidence Comment Testing')
GO
SET IDENTITY_INSERT [dbo].[FreeTextDataDraft] OFF
GO
SET IDENTITY_INSERT [dbo].[NodeAnswersDraft] ON
GO
INSERT [dbo].[NodeAnswersDraft] ([NodeDataID], [FWID], [MemberID], [NodeID], [QuaterID], [MaturityLevelID], [CurrentStatsID], [ActionPlanID], [CommentID], [EvidenceCommentID]) VALUES (19, 137, 1, 6628, 13, 502, 1, 2, 3, 4)
GO
INSERT [dbo].[NodeAnswersDraft] ([NodeDataID], [FWID], [MemberID], [NodeID], [QuaterID], [MaturityLevelID], [CurrentStatsID], [ActionPlanID], [CommentID], [EvidenceCommentID]) VALUES (20, 137, 1, 6627, 13, 501, 5, 6, 7, 8)
GO
SET IDENTITY_INSERT [dbo].[NodeAnswersDraft] OFF
There is a another database named ‘Parent’ with same tables & schema except for [NodeAnswersDraft], the column
[EvidenceCommentID] is not present & a new column [TempNodeDataID] is present.
Also, FreeTextDataDraft table does not have IDENTITY value generation, but NodeAnswersDraft have IDENTITY value generation for NodeDataID.
The schema in ‘Parent’ Db is below:
CREATE TABLE [dbo].[FreeTextDataDraft](
[FreeTextID] [bigint] NOT NULL,
[NodeDataID] [bigint] NULL,
[TextData] [nvarchar](max) NULL,
CONSTRAINT [PK_FreeTextDataDraft] PRIMARY KEY CLUSTERED
(
[FreeTextID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[NodeAnswersDraft](
[NodeDataID] [bigint] IDENTITY(1,1) NOT NULL,
[FWID] [bigint] NULL,
[MemberID] [bigint] NULL,
[NodeID] [bigint] NULL,
[QuaterID] [tinyint] NULL,
[MaturityLevelID] [bigint] NULL,
[CurrentStatsID] [bigint] NULL,
[ActionPlanID] [bigint] NULL,
[CommentID] [bigint] NULL,
[TempNodeDataID] [bigint] NULL,
CONSTRAINT [PK_NodeAnswersDraft] PRIMARY KEY CLUSTERED
(
[NodeDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
While moving data from Child to Parent DBs, NodeAnswersDraft table will be executed first, then FreeTextDataDraft.
Here while moving data, new identity value will be created for NodeDataId column. The old value will be saved, in TempNodeDataID column.
The query I used for moving data between databases for is below:
MERGE INTO Parent.dbo.NodeAnswersDraft TR
using (SELECT NA.NodeDataID,
NA.FWID,
NA.MemberID,
NA.NodeID,
NA.QuaterID,
NA.MaturityLevelID,
NA.CurrentStatsID,
NA.ActionPlanID,
NA.CommentID
FROM Child.dbo.NodeAnswersDraft NA
) SC
ON (TR.FWID = SC.FWID AND TR.MemberID = SC.MemberID AND TR.NodeID = SC.NodeID AND TR.QuaterID = SC.QuaterID)
WHEN matched THEN
UPDATE SET
TR.FWID = SC.FWID,
TR.MemberID = SC.MemberID,
TR.NodeID = SC.NodeID,
TR.QuaterID = SC.QuaterID,
TR.MaturityLevelID = SC.MaturityLevelID,
TR.CurrentStatsID = SC.CurrentStatsID,
TR.ActionPlanID = SC.ActionPlanID,
TR.CommentID = SC.CommentID
WHEN NOT matched THEN
INSERT (
FWID,
MemberID,
NodeID,
QuaterID,
MaturityLevelID,
CurrentStatsID,
ActionPlanID,
CommentID,
TempNodeDataID )
VALUES (
SC.FWID,
SC.MemberID,
SC.NodeID,
SC.QuaterID,
SC.MaturityLevelID,
SC.CurrentStatsID,
SC.ActionPlanID,
SC.CommentID,
SC.NodeDataID );
After this, data from FreeTextDataDraft will be moved across the database. My requirement is that when it is moved, the newly generated identity value of NodedataId should be updated in NodedataId column in FreeTextDataDraft.
I have used the following query for this:
MERGE INTO Parent.dbo.FreeTextDataDraft TR
using (SELECT FT.FreeTextID,
FT.NodeDataID,
FT.TextData,
NA.NodeDataID AS NodeAnsNode,
NA.TempNodeDataID
FROM Child.dbo.FreeTextDataDraft FT
INNER JOIN Parent.dbo.NodeAnswersDraft NA ON FT.NodeDataID = NA.TempNodeDataID
) SC
ON TR.FreeTextID = SC.FreeTextID
WHEN matched THEN
UPDATE SET
TR.NodeDataID = SC.NodeAnsNode,
TR.TextData = SC.TextData
WHEN NOT matched THEN
INSERT (FreeTextID,
NodeDataID,
TextData )
VALUES ( SC.FreeTextID,
SC.NodeAnsNode,
SC.TextData ) ;
Is there any way I can achieve this, without introducing the intermediate column TempNodeDataID column in NodeAnswersDraft ?
