两个表之间的一个查询 [英] One Query Between 2 Tables

问题描述

嗨


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 <来实现此目的/ code>语句从两个表中获取单个数据集。您可以使用 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，< span class =code-keyword> 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表中添加具有相同名称，大小和数据类型的列。简单<！/ BLOCKQUOTE>

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!

这篇关于两个表之间的一个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！