SQLTeam.com | Weblogs | Forums

Updating a column without the help of a intermediate column

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 ?

@ harishgg1 Thanks for the links
My problem is to avoid the intermediate column TempNodeCode & update another table column.
I have given the schema & test data.
Please have a look.
Experts here, if you can lend a helping hand, I will be grateful.

hi

if you want to avoid
..... TempNodeCode

it is used in join !!!! to move data into Parent.dbo.FreeTextDataDraft
from child.dbo.FreeTextDataDraft

options

  1. you can use some other column
  2. avoid the join !!!
  3. use something else instead of MERGE statement

dont know what your requirement is !!! :slight_smile: :slight_smile: hope this helps