如何将一个表数据插入并更新到sqldatabase中的另一个表 [英] How to insert and update one table data to another table in sqldatabase

问题描述

大家好，



我有两张Master table和两张trans table ...



在Master1表Ⅰ有像



（PK）字段MasterId&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP SrNo



在TRANS1表I有两个领域

（PK）TRANSID&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP MasterId（Master1的FK）



现在我有Master1和反-1重复表为Master2和TRANS2



Master2表字段



（PK）MasterId&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP SrNo



Trans2桌面



（PK）TransId                          &


例如

---------------------------- -------------------------------------------------- -----------------

Master1 = MasterId    SrNo

     &n BSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1个

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2



TRANS1 = TRANSID&NBSP&NBSP&NBSP MasterId

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP页 1

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2

--------------------------------------- -------------------------------------------------- ------

Master2 = MasterId&NBSP&NBSP&NBSP SrNo

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1个

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&nb的SP&NBSP&NBSP 2



TRANS2 = TRANSID&NBSP&NBSP&NBSP MasterId

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP页 1

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2



现在我在Master1和Trans2表数据中尝试两个插入Master2表数据在Trans1中我成功完成但问题是，当我在Trans1中插入Trans2数据时，MasterId是相同的但是我想要按顺序显示它。



输出我在Master1和Trans1中插入数据后得到

Master1 = MasterId   SrNo

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1个

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&N BSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 3

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 4



TRANS1 = TRANSID&NBSP&NBSP&NBSP&NBSP MasterId

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 1&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP页 1

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 3&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP页 1

&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP& NBSP&NBSP&NBSP&NBSP 4&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP&NBSP 2





见的MasterId在反-1是1,2,1,2，但希望它在序列我该做的请帮助我



谢谢和登记

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

推荐答案

我认为这是你应该做的，但我可能会关闭。



（我假设所有桌子都有一个自动增量ID）



这是简单的部分，将 Master2 中的值附加到 Master1

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

insert into Master1 (SrNo) select SrNo from Master2;

然后我认为你应该删除所有Trans1中的行然后插入新的。

Then I think you should actually delete all rows in Trans1 and then insert new ones.

delete from Trans1;
insert into Trans1 (Master1_MasterID) select MasterID from Master1;

原因是值在表中，Trans2已经没有任何意义，所以没有必要合并Trans1和Trans2。



正如我所说的，也许我完全不在这里，想到简单。

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.

您好，



使用以下查询

Hi,

Use below Query

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.

这篇关于如何将一个表数据插入并更新到sqldatabase中的另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！