January 12, 2020 at 6:58 pm
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 ?
January 13, 2020 at 12:10 am
How is the data model originally populated? The examples provided set IDENTITY_INSERT ON. Are the examples representative of how inserts into the Child db tables occur in actual usage cases? Or are there inserts that make use of the IDENTITY column properties? It seems to me the "copying" between servers could be eliminated by a stored procedure that inserts into all of the tables within a single transaction. Alternately, is it not possible to use the built in db mirroring?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 13, 2020 at 8:58 am
Table schema I have provided, is the one required. IDENTITY property is defined on that table. Hence I am using IDENTITY_INSERT ON.
Data is populated from the application & not inserted manually. I have provided schema & test data here.
Also, for moving table data from one DB to another, I am using SSIS Execute SQL tasks with the SQL code I have provided here. The job is scheduled & run at intervals defined.
My problem is to avoid the intermediate column TempNodeCode in NodeAnswersDraft table & update NodeDataId in FreeTextDataDraft table with the newly generated NodeDataId in NodeAnswersDraft
Please see the last SQL block I posted, where I have used the column TempNodeCode from NodeAnswersDraft table. I am reposting that part here.
FROM Child.dbo.FreeTextDataDraft FT
INNER JOIN Parent.dbo.NodeAnswersDraft NA ON FT.NodeDataID = NA.TempNodeDataID
Please have a look.
Experts here, if you can lend a helping hand, I will be grateful.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply