如何从同一个数据库中的两个不同表输入一个表的值？ [英] how to enter the values to one table from two different tables in same database?

问题描述

朋友我的数据库中有三个表，Table1有10列，Table2有1列，I创建包含11列的Table3，如何在Table3中插入Table1值前10列，表2中的表2值最后专栏。

Hi, Friends I have Three Tables in my database, Table1 has 10 columns, Table2 Has 1 column, I Create the Table3 that contains 11 columns, How to insert Table1 values in Table3 first 10 columns and table 2 value in table3 last column.

insert into Table3(col1,col2,....,col11) VALUES SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2

我知道这段代码不是正确，请帮我纠正它

I know this code is not correct, help me to correct it

推荐答案

一个包含一列的表不能与另一个表中的数据相关联 - 除非它是一个外键列，但是外键数据已经在另一个表中了！ ：笑：



SQL行不是序数：除非查询中存在特定的ORDER BY子句，否则SQL可以按任何顺序返回行。 - 因此，如果从两个表中选择数据，则有两个表以特定顺序输入数据并不保证对数据的任何排序 - 并且使用相同查询获得的顺序不一定与相同的查询稍后运行，如果SQL因为任何理由重新组织了数据，那么它应该是。



所以在实践中，你不能这样做 - 在至少不在SQL中 - 因为你需要一个JOIN来组合表而你没有链接字段。



作为一次性，你可以使用C＃或VB （例如）在单​​独的查询中选择每个表中的所有行，然后编写第三个查询以组合它们 - 或者您可以选择SELECT到两个临时表中，每个表上有一个ROWCOUNT，并在JOIN的链接字段中使用它。



但不管怎样，你必须非常非常小心地确定你最终得到的数据，并仔细检查以确保数据的完整性。



我？我将在设计器的原始表中添加一个新行，并再次从第二个表中输入新数据，以确保每次都转到右侧。

A table with one column can't be associated with data in a different table - unless it's a foreign key column, but then the foreign key data is already in the other table already! :laugh:

SQL rows aren't ordinal: SQL is at liberty to return rows in any order it finds convenient unless a specific ORDER BY clause is present in the query - so having two tables where the data has been entered in a specific order does not guarantee any ordering to the data when it is selected from the two tables - and the order you get with the same query is not necessarily going to be the same as the same query run a second later, if SQL has reorganised the data for any reason it feels it should.

So in practice, you can't do it - at least not in SQL - as you need a JOIN to "combine" the tables and you have no linking field.

As a one off, you could use C# or VB (for example) to SELECT all of the rows from each table in separate queries and then write a third query to combine them - or you could SELECT then into two temporary tables with a ROWCOUNT on each and use that at the linking field for the JOIN.

But either way, you are going to have to be very, very careful of exactly what data you end up with and check it very carefully to ensure your data integrity.

Me? I'd add a new row to the original table in the designer, and enter the "new data" from the second table again to make sure it goes to the right row each time.

这篇关于如何从同一个数据库中的两个不同表输入一个表的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！