Click here to Skip to main content
14,868,570 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello to All,

I have two Master table and two trans table ...

In Master1 table i have field like

(PK)MasterId           SrNo

In Trans1 table i have two field
(PK)TransId           MasterId(FK of Master1)

Now I have duplicate table of Master1 and Trans1 as Master2 and Trans2

Master2 Table Field

(PK)MasterId           SrNo

Trans2 Table Field

(PK)TransId           MasterId(FK of Master2)


For Example
-----------------------------------------------------------------------------------------------
Master1 = MasterId     SrNo
                     1
                     2

Trans1 = TransId     MasterId
                     1            1
                     2            2
-----------------------------------------------------------------------------------------------
Master2 = MasterId     SrNo
                     1
                     2

Trans2 = TransId     MasterId
                     1            1
                     2            2

Now i try two insert Master2 table data in Master1 and Trans2 table data in Trans1 i done it successfully but the problem is that, when i inserted Trans2 data in Trans1 at that time MasterId is same but i want to show it in a sequence.

Output I get after inserted data in Master1 and Trans1
Master1 = MasterId    SrNo
                     1
                     2
                     3
                     4

Trans1 = TransId      MasterId
                     1            1
                     2            2
                     3            1
                     4            2


See the MasterId in Trans1 is 1,2,1,2 but want it in sequence what i do for that please help me

Thanks and Regads
Posted
Updated 7-Jul-14 22:17pm
v5
Comments
[no name] 8-Jul-14 2:48am
   
what problem u facing ? mention it clearly
George Jonsson 8-Jul-14 2:57am
   
If QueTransID is a PK in another table and you increment this value with 1 in your joined table, which row will the incremented QueTransID point to in the foreign table?
For me this operation doesn't make sense, but I might be missing something.
Jay1902 8-Jul-14 3:01am
   
I hope now you understand my question it was my mistake to explain it clearly...
George Jonsson 8-Jul-14 3:16am
   
Hmm, I still don't get the point of updating the FK, unless you update that table as well. Otherwise you will have a FK that refers to a non existing ID.
Can you show the other table as well, where TableTransId is PK.
Jay1902 8-Jul-14 4:18am
   
I update my question as you say please help me if possible
George Jonsson 8-Jul-14 4:30am
   
Maybe a bit clearer, maybe.
What does your SQL code look like?
Easier to see where it should be changed.
Jay1902 8-Jul-14 4:36am
   
is there any way to update that using update statement

I think this is what you should do, but I might be off.

(I have assumed an Auto Increment ID for all tables)

This is the easy part, appending the values from Master2 into Master1
SQL
insert into Master1 (SrNo) select SrNo from Master2;


Then I think you should actually delete all rows in Trans1 and then insert new ones.
SQL
delete from Trans1;
insert into Trans1 (Master1_MasterID) select MasterID from Master1;


The reason is that the values in table Trans2 has no meaning anymore, so no point in merging Trans1 and Trans2.

As I said, maybe I am totally off here and think to simple.
   
v5
Hi,

Use below Query

SQL
Select *, ROW_NUMBER() over (order by (select 0)) RN into #temp from Master2
declare @i int, @Count int
Set @i = 1
Select @Count = COUNT(*) from #temp
while(@i<=@Count)
Begin
    Insert into Master1 (SrNo) Select SrNo from #temp where RN = @i
    Insert into Tran1(MasterID) Select SCOPE_IDENTITY()
    Set @i= @i+1
End


If you have any confusion or question then you can ask me without any hesitation.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900