How to insert and update one table data to another table in sqldatabase

Question

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 7-Jul-14 20:36pm

Jay1902

Updated 7-Jul-14 22:17pm

v5

Add a Solution

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

2 solutions

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.

Posted 7-Jul-14 22:57pm

George Jonsson

Updated 8-Jul-14 2:18am

v5

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)

Sarvesh Kumar Gupta · Accepted Answer · 2014-07-08T02:47:00

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.