15,959,706 members
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

Posted
Updated 7-Jul-14 22:17pm
v5
[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

## Solution 1

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

## Solution 2

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.