如何根据其他表中的内容生成表内容？

时间：2023-01-20 20:32:59

I have many-to-many relationship and 3 tables: A, B, C.

我有多对多的关系和3个表:A,B,C。

A and C each have only 1 primary key and B has compound primary key being foreign keys to tables A and C and nothing more.

A和C每个只有一个主键,B有复合主键是表A和C的外键,仅此而已。

Tables A and C are already filled with data and each of them has 100 rows and now I would like to insert a row to B consisting of random key from A and random key from C. How to achieve that?

表A和C已经填充了数据,每个都有100行,现在我想在B中插入一行,包括来自A的随机密钥和来自C的随机密钥。如何实现?

This is what I tried but I don't know how to get currently processed row in select statement and I'm stuck now... I don't even know if this kind of statement is actually alright or there's better approach?

这是我尝试但我不知道如何在select语句中获取当前处理的行并且我现在卡住了...我甚至不知道这种陈述是否实际上是好的还是有更好的方法?

INSERT INTO B(bKey1, bKey2) 
VALUES(
(select a.key1 from A a where Convert(int, rand() * 100.0) = currentRow? ),
 (select c.key2 from C c where Convert(int, rand() * 100.0) = currentRow? )
 );

Even after inserting fixed value as 1 or 2 instead of "currentRow?" I'm getting following errors and don't understand why:

即使插入固定值为1或2而不是“currentRow?”我遇到了以下错误,并且不明白为什么:

Cannot insert the value NULL into column 'bKey1', table 'MyDataBase.dbo.B'; column does not allow nulls. INSERT fails.

无法将值NULL插入列'bKey1',表'MyDataBase.dbo.B';列不允许空值。 INSERT失败。

I would be really grateful for any help!

我真的很感激任何帮助!

1 个解决方案

#1

1  

To get a random record from each table, you can cross join the tables together, use top 1, and order by newid():

要从每个表中获取随机记录,您可以将表交叉连接在一起,使用top 1,并按newid()排序:

INSERT INTO B(bKey1, bKey2) 
    select top 1
        a.key1,
        c.key2
    from A a
        cross join C c
    order by newid()

#1

1  

To get a random record from each table, you can cross join the tables together, use top 1, and order by newid():

要从每个表中获取随机记录,您可以将表交叉连接在一起,使用top 1,并按newid()排序:

INSERT INTO B(bKey1, bKey2) 
    select top 1
        a.key1,
        c.key2
    from A a
        cross join C c
    order by newid()
标签：

相关文章