how to access current updated id of maintable with reference of oldmaintable parent id

Question

2.50/5 (2 votes)

See more:

My new table ----dbo.MainTable data->
 
Id   ModuleId      MenuDescription         ParentId     AssemblyName
 
161	8	Manufacture Order			
162	8	MO Release		168	
163	8	Short Close		161	
166	8	Carton			
167	8	Manufacture Order	         161	
168	8	Export		         167	ProductionUI.dll

dbo.OldMainTable data->
 
Id   ModuleId      MenuDescription         ParentId     AssemblyName
 
161	8	M O		
162	8	M O R	                 169	
163	8	S C	                 162	
164	8	M R		
165	8	P C T		
166	8	C		
167	8	M O	                 164	
168	8	E	                 164	UI.dll
169	8	DTA	                 164

in above both table parent id is taken from Id
After merging both table through cursor my updated new table looks like--

Id   ModuleId      MenuDescription         ParentId     AssemblyName
169     8              M O
170     8              M O R                169
171     8              S C                  162
172     8              M R
173     8              P C T
174     8              C
175     8              M O                  164
176     8              E                    164           UI.dll
177     8              DTA                  164   
161      8	Manufacture Order			
162	8	MO Release		168	
163	8	Short Close		161	
166	8	Carton			
167	8	Manufacture Order	         161	
168	8	Export		         167	ProductionUI.dll

In above new table u can see the id of OldMainTable starting from the max of MainTabledata (starting from 169 and so on)but problem in the o/p after merging that parentid associated with id, in old table u can see(id 162->parentid 169,id 163->162,167->164,168->164,169->164 ) but after merging, id changed and start from(169 to 177,the parent id must be like id 170-> parentid 177, 171->170,175->172, 176->172,177->172 also changed) but u can see it not happens, parentid not update so how can i update parent id according to id.

I have one clue but unable to implement that,the clue is-- i will hold the parentid of oldMainTable in a variable,then look for updated value for this old value and place it at merging table. if any other idea so plz help.

one thing the query i m using for merging is--->

ALTER PROCEDURE [dbo].[SP_ApplMenuMaster_Merge]	
AS
BEGIN
DECLARE 
@Id VARCHAR(30),@ModuleId varchar(30),@MenuDescription VARCHAR(100),@ParentId varchar(30),@AssemblyName varchar(max)

Declare cursor2 cursor for
	
Select 
Id,ModuleId,MenuDescription,ParentId,AssemblyName from OldMainTable 
OPEN cursor2
FETCH NEXT FROM cursor2 
INTO @Id,@ModuleId,@MenuDescription,@ParentId,@AssemblyName
WHILE @@FETCH_STATUS = 0
BEGIN
Select @Id = max(convert(int,id))+1 from MainTable
Select @ModuleId=ModuleId from OldMainTable where Id=@Id 			
Select @MenuDescription = MenuDescription from OldMainTable where Id=@Id
Select @ParentId=ParentId from OldMainTable where Id=@Id
Select @AssemblyName=AssemblyName from OldMainTable where Id=@Id

 insert into MainTable values 
(@Id,@ModuleId,@MenuDescription, @ParentId, @AssemblyName)

FETCH NEXT FROM cursor2
INTO @Id,
@ModuleId,
@DisplayName,
@ParentId,
@AssemblyName,
END
CLOSE cursor2
DEALLOCATE cursor2
END

plz help me, to update parentid accoriding to updated id,based on old parentid.
else
according to u,if possible so plz with complete query,plzzz someone help

Posted 1-Nov-12 21:37pm

StackQ

Updated 1-Nov-12 22:42pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shanalal Kasim · Answer 1 · 2012-11-03T01:32:00

Solution 1

Please change your merging logic like below

SQL

insert into dbo.MainTable (id,ModuleId,MenuDescription,ParentId ,AssemblyName)

select ( select max(convert(int,id))+1 from MainTable),ModuleId,MenuDescription,
      case  ISNULL ( ParentId , 0 )
      when 0 then  null
      else   ( select max(convert(int,id))+1 from MainTable) end, AssemblyName
from dbo.OldMainTable

Posted 3-Nov-12 1:32am

Shanalal Kasim

Updated 4-Nov-12 21:25pm

v3

Comments

StackQ 5-Nov-12 2:20am

Showing error ----(error is)--->

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '>'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'else'.

Shanalal Kasim 8-Nov-12 6:57am

If it helped you to solve your problem , mark it as answer

StackQ · Answer 2 · 2012-11-04T20:26:00

ALTER PROCEDURE [dbo].[SP_ApplMenuMaster_Merge]	
AS
BEGIN
DECLARE 
@ApplMenuMasterId VARCHAR(30),@ModuleMasterId varchar(30),@DisplayName VARCHAR(100),@ValueName varchar(100),@ParentMenuId varchar(30),@AssemblyName varchar(100),@ExeClassName varchar(100),
@IsRootMenu varchar(1),@CallMethod varchar(100), @Parameter varchar(100),
@Remark varchar(100),@IsEnable varchar(1), 
@IsVisible varchar(1),@SeqNo numeric(18,0),
@AddedBy varchar(50),@AddedDt datetime,
@ModifiedBy varchar(50),@ModifiedDt datetime,
@MenuType varchar(30) 

Declare cursor2 cursor for
	
Select ApplMenuMasterId,ModuleMasterId,DisplayName,ValueName,ParentMenuId,
AssemblyName,ExeClassName,IsRootMenu,CallMethod,Parameter,Remark,
IsEnable,IsVisible,SeqNo,AddedBy,AddedDt,ModifiedBy,ModifiedDt,
MenuType from ApplMenuMaster_Client 
OPEN cursor2
FETCH NEXT FROM cursor2 
INTO @ApplMenuMasterId,
@ModuleMasterId,
@DisplayName,
@ValueName,
@ParentMenuId,
@AssemblyName,
@ExeClassName,
@IsRootMenu,
@CallMethod,
@Parameter,
@Remark,
@IsEnable,
@IsVisible,
@SeqNo,
@AddedBy,
@AddedDt,
@ModifiedBy,
@ModifiedDt,
@MenuType
WHILE @@FETCH_STATUS = 0
BEGIN
Select @ApplMenuMasterId = max(convert(int,ApplMenuMasterId))+1 from ApplMenuMaster_Copy
Select @ModuleMasterId=ModuleMasterId from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId 			
Select @DisplayName = DisplayName from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @ValueName=ValueName from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @ParentMenuId=ParentMenuId from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId 
Select @AssemblyName=AssemblyName from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @ExeClassName=ExeClassName from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @IsRootMenu=IsRootMenu from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @CallMethod=CallMethod from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @Parameter=Parameter from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @Remark=Remark from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @IsEnable=IsEnable from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @IsVisible=IsVisible from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @SeqNo=SeqNo from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @AddedBy=AddedBy from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @AddedDt=AddedDt from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @ModifiedBy=ModifiedBy from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @ModifiedDt=ModifiedDt from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId
Select @MenuType=MenuType from ApplMenuMaster_Client where ApplMenuMasterId=@ApplMenuMasterId

 insert into ApplMenuMaster_Copy values 
(@ApplMenuMasterId,@ModuleMasterId,@DisplayName, @ValueName, @ParentMenuId, @AssemblyName, @ExeClassName, @IsRootMenu, @CallMethod, @Parameter, @Remark, 
@IsEnable,@IsVisible, @SeqNo,@AddedBy, @AddedDt,@ModifiedBy,@ModifiedDt,@MenuType )

update ApplMenuMaster_Copy set ParentMenuId=(select ApplMenuMasterId from ApplMenuMaster_Copy  WHERE 
ApplMenuMasterId =(SELECT ApplMenuMasterId from ApplMenuMaster_Copy where DisplayName =(select DisplayName from ApplMenuMaster_Client where
 ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId))
and
ModuleMasterId =(Select ModuleMasterId from ApplMenuMaster_Client  where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and ValueName=(Select ValueName from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and AssemblyName=(Select AssemblyName from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and ExeClassName=(Select ExeClassName from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and IsRootMenu=(Select IsRootMenu from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and CallMethod=(Select CallMethod from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and Parameter=(Select Parameter from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and Remark=(Select Remark from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and IsEnable=(Select IsEnable from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and IsVisible=(Select IsVisible from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and SeqNo=(Select SeqNo from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and AddedBy=(Select AddedBy from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and MenuType=(Select MenuType from ApplMenuMaster_Client where ApplMenuMasterId =(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId=@ApplMenuMasterId) )
and ApplMenuMasterId > 822
))where ParentMenuId=(Select ParentMenuId from ApplMenuMaster_cOPY WHERE ApplMenuMasterId >822 and ApplMenuMasterId=@ApplMenuMasterId) 
 and ApplMenuMasterId=@ApplMenuMasterId

FETCH NEXT FROM cursor2
INTO 
@ApplMenuMasterId,
@ModuleMasterId,
@DisplayName,
@ValueName,
@ParentMenuId,
@AssemblyName,
@ExeClassName,
@IsRootMenu,
@CallMethod,
@Parameter,
@Remark,
@IsEnable,
@IsVisible,
@SeqNo,
@AddedBy,
@AddedDt,
@ModifiedBy,
@ModifiedDt,
@MenuType
END
CLOSE cursor2
DEALLOCATE cursor2

END

GO

actually in my table there are 19 columns and 2 tables, ApplMenuMaster_Copy and ApplMenuMaster_Client. I have to merge client table to _copy table.So finally this is working for my problem.

My question is only sample of my problem, so u will see diff column name in my solution ..but thnx for helping me..