两个表之间的一个查询

本文介绍了两个表之间的一个查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

嗨 i有2个表：Serials，ProdConfirm i想要从Serials表到ProdConfirm表添加2列，表中的两列相似（我基本上想要将2列复制到另一个表格。 i已经提出了sql语法，但我不能同时添加两个表格 可以帮助我吗？ 代码：

INSERT INTO ProdConfirm（Serial，[ Group ]） SELECT Serial，[ Group ] FROM 序列号 WHERE [组] =？

i我正在使用oledb

解决方案

如果我理解正确，你Table_1中有两列，Table_2中有两列，两列都具有相同的数据类型。您希望将这些组合成一个数据集，然后将结果插入Table_3。 您可以通过首先创建 SELECT 语句从两个表中获取单个数据集。您可以使用 UNION 执行此操作：

SELECT Coll AS Column_A，Col2 AS Column_B FROM Table_1 UNION SELECT Col3 AS Column_A，Col4 AS Column_B FROM 表2

这将返回一个包含两列的数据集，由两个表中的数据交集填充。也就是说，每一行都是不同的。如果您需要获取任何重复项，请使用 UNION ALL 您不需要为列名称添加别名，但如果相同的位置（例如，Col1和Col3）具有不同的名称在不同的表中，数据集中的结果列将没有任何名称。 第二步是将此用于插入。结果可能如下所示：

INSERT INTO Prod_Confirm（Serial， Group ）（ SELECT Col1 AS Serial，Col2 AS 组 FROM Table_1 UNION SELECT Col3 AS Serial，Col2 AS 组 FROM Table_2 ）

SQL将运行选择，然后使用结果数据集进行插入。

1.首先查看数据类型和大小 （例如varchar（36））序列表中所需的列 使用：

sp_help Serials

2.然后b使用alter table querry在ProdConfirm表中添加具有相同名称，大小和数据类型的列。简单

Hi i have 2 tables : Serials, ProdConfirm i want to add 2 columns from Serials table to ProdConfirm table and both columns in tables are similar (i basically want to copy 2 columns to another table) i have came up with a sql syntax but i cant add two tables at the same time can some one help me ? code :

INSERT INTO ProdConfirm (Serial, [Group]) SELECT Serial,[Group] FROM Serials WHERE [Group] = ?

i am using oledb

解决方案

If I understand you correctly, you have two columns in Table_1 and two columns in Table_2, both with the same data types. You want to combine these into a single data set, then insert the result into Table_3. You can approach this by first creating a SELECT statement to get the single data set from the two tables. You can do this with a UNION:

SELECT Coll AS Column_A, Col2 AS Column_B FROM Table_1 UNION SELECT Col3 AS Column_A, Col4 AS Column_B FROM Table_2

This will return a data set with two columns, populated by the intersection of data from the two tables. That is to say, each row will be distinct. If you need to get any duplicates, instead use UNION ALL You do not need to alias the column names, but if the same position (say, Col1 and Col3) have different names in the different tables, the resulting column in the data set will not have any name. The second step is to use this for your insert. The result might look like this:

INSERT INTO Prod_Confirm (Serial, Group) ( SELECT Col1 AS Serial, Col2 AS Group FROM Table_1 UNION SELECT Col3 AS Serial, Col2 AS Group FROM Table_2 )

SQL will run the selection, then use the resulting data set for the insert.

1.First see the data types and size ( e.g varchar(36) ) of the desired columns in Serials table by using:

sp_help Serials

2.Then by using alter table querry add those columns with same name, size, and data types in ProdConfirm table. simple!