如何使用oldmaintable父ID的引用访问maintable的当前更新ID [英] how to access current updated id of maintable with reference of oldmaintable parent id
问题描述
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
上面两个表的父ID均取自ID
通过光标合并两个表后，更新后的新表看起来像-
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
在上面的新表中，您可以从MainTabledata的最大值(从169开始，以此类推)开始查看OldMainTable的ID，但是在合并与ID相关联的父ID之后，O/P中的问题，您可以在旧表中看到(ID 162- & parentid 169，id 163-> 162,167-> 164,168-> 164,169-> 164)，但合并后，id更改并从(169到177开始，其父id必须类似于id 170-> parentid 177、171-> 170,175-> 172、176-> 172,177-> 172也已更改)，但您看不到这种情况，parentid不会更新，所以我如何根据id更新父id.
我有一个线索，但无法实现，线索是-我会将oldMainTable的父代保存在变量中，然后为该旧值寻找更新后的值并将其放在合并表中.如果有其他想法，请帮助.
我用于合并的查询的一件事是--->
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
请帮助我，根据旧的父母身分证将父母身分证更新为更新的身分证.
其他
根据您的说法，如果可能的话请使用完整的查询，请zzzzz有人帮助
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
推荐答案
请更改您的合并逻辑，如下所示
Please change your merging logic like below
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
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
实际上，在我的表中有19列和2个表，即ApplMenuMaster_Copy和ApplMenuMaster_Client.我必须将客户端表合并到_copy table.So最后这解决了我的问题.
我的问题只是我问题的一个示例，因此您将在解决方案中看到diff列名..但是thnx可以帮助我..
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..
这篇关于如何使用oldmaintable父ID的引用访问maintable的当前更新ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
