附表 [英] Appending tables
问题描述
我想知道是否可以将两张桌子附加到
的第三张桌子上。
例如，考虑这两张桌子
表1
----------------------- ---------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 |
---------------------------------------- ----------------------
| PRT1 | PartA | abc | xyz |
| PRT2 | PartB | def | aaa |
| PRT3 | PartC | ghi | bbb |
---------------------------------------- ----------------------
表2
------ -------------------------------------------------- -------
| Cat_num | Cat_name | SDsc1 | SDsc2 |
---------------------------------------- -----------------------
| CAT1 | CatalogA | abc | xyz |
| CAT2 | CatalogB | def | aaa |
| CAT3 | CatalogC | ghi | bbb |
---------------------------------------- -----------------------
现在，我想追加他们来获取这个：
表3
------------------------------------ -------------------------------------------------- ---------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
---------------------------------------- -------------------------------------------------- -----------------------------------
| PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
---------------------------------------- -------------------------------------------------- ----------------------------------
表3中的空白很好，空白。
现在可以做到吗？
等待你的回复，
问候，
Shwetabh
Shwetabh写道：
我想知道是否可以将两张表附加到第三张表中。
例如，考虑这两张表
表1
- -------------------------------------------------- ----------
| Part_num | Prt_name | Desc1 | Desc2 |
-------------------------------------------- ------------------
| PRT1 | PartA | abc | xyz |
| PRT2 | PartB | def | aaa |
| PRT3 | PartC | ghi | bbb |
-------------------------------------------- ------------------
表2
------------------ ---------------------------------------------
| Cat_num | Cat_name | SDsc1 | SDsc2 |
-------------------------------------------- -------------------
| CAT1 | CatalogA | abc | xyz |
| CAT2 | CatalogB | def | aaa |
| CAT3 | CatalogC | ghi | bbb |
-------------------------------------------- -------------------
现在，我想附加它们来获得这个：
表3
-------------------------------------------- -------------------------------------------------- -------------------------------
| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
-------------------------------------------- -------------------------------------------------- -------------------------------
| PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
-------------------------------------------- -------------------------------------------------- ------------------------------
表3中的空白是空白的。
现在可以做到吗？
等待你的回复，
问候，
Shwetabh
我的浏览器没有很好地显示你的表3所以我不太确定哪些数据会进入哪一列。一般来说，你可以使用UNION合并
这样的表：
SELECT part_num，prt_name，desc1，...
FROM表1
UNION ALL
SELECT NULL，NULL，cat_name，...
FROM Table2;
UNION中的每个SELECT列表必须具有相同的列数
，并且每列必须由兼容的数据类型组成。在你的情况下
似乎最重要的问题是Table3的关键是什么？它b / b
我不清楚它是否有钥匙。
-
David Portas，SQL服务器MVP
只要有可能，请发布足够的代码来重现您的问题。
包含CREATE TABLE和INSERT语句通常会有所帮助。
说明您正在使用的SQL Server版本，并指定任何错误消息的内容
。
SQL Server联机丛书：
http://msdn2.microsoft.com/library/ m ... S，SQL.90）.aspx
-
Shwetabh（sh ** ********@gmail.com）写道：表3
----------------- -------------------------------------------------- -------
--------------------------------------- ------------ | Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
-------------------------------------------- ------------------------------
---------------- ----------------------------------- | PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
-------------------------------------------- ------------------------------
---------------- ----------------------------------
表3中的空白很好，空白。
现在可以做到吗？
根据您发布的样本数据判断，您想要的是什么？ >
SELECT a.Part_num，a.Prt_name，a.Desc1，a.Desc2，b.Cat_num，
b.Cat_name，b.SDsc1，b.SDcs2
FROM table1 a
JOIN table2 b ON a.Desc1 = b.SDsc1
AND b.Desc2 = b.SDcs2
但是我不能说加入一个描述栏是很有意义的。
也许你需要多考虑一下你实际看到的内容
for和你想要实现的目标。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
似乎表3搞得搞砸了。
所以我会在这里给出表格的架构定义：
表1：
CREATE TABLE TABLE1
（
PART_NUM varchar（10）主键，
PRT_NAME VARCHAR（10），
DESC1 VARCHAR（20），
DESC2 VARCHAR（20）
）
表2：
CREATE TABLE TABLE2
（
PART_NUM varchar（10）主键，
CAT_NUM VARCHAR（10），
CAT_NAME VARCHAR（10），
SDESC1 VARCHAR（20），
SDESC2 VARCHAR（ 20）<无线电通信/>
）
现在结果表应该具有以下模式：
CREATE TABLE TABLE3
（
PART_NUM varchar（10）主键，
PRT_NAME VARCHAR（10），
DESC1 VARCHAR（20），
DESC2 VARCHAR（20），
CAT_NUM VARCHAR（10），
CAT_NAME VARCHAR（10），
SDESC1 VARCHAR（ 20），
SDESC2 VARCHAR（20）
）
此架构将以编程方式创建。
现在我的问题是，如果有可能，我怎么能从表3中的table1和table2插入记录？
我希望我现在已经制作了更清楚的事情。
等待你的回复，
问候，
Shwetabh
Hi,
I wanted to know if it is possible to do to append two tables into a
third table.
For example, consider these two tables
Table 1
--------------------------------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 |
--------------------------------------------------------------
| PRT1 | PartA | abc | xyz |
| PRT2 | PartB | def | aaa |
| PRT3 | PartC | ghi | bbb |
--------------------------------------------------------------
Table 2
---------------------------------------------------------------
| Cat_num | Cat_name | SDsc1 | SDsc2 |
---------------------------------------------------------------
| CAT1 | CatalogA | abc | xyz |
| CAT2 | CatalogB | def | aaa |
| CAT3 | CatalogC | ghi | bbb |
---------------------------------------------------------------
Now, I want to append them to get this :
Table 3
-----------------------------------------------------------------------------------------------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
-----------------------------------------------------------------------------------------------------------------------------
| PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
----------------------------------------------------------------------------------------------------------------------------
The blanks in Table 3 are , well ,blank.
Now can it be done or not?
Awaiting your replies,
Regards,
Shwetabh
Shwetabh wrote:Hi,
I wanted to know if it is possible to do to append two tables into a
third table.
For example, consider these two tables
Table 1
--------------------------------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 |
--------------------------------------------------------------
| PRT1 | PartA | abc | xyz |
| PRT2 | PartB | def | aaa |
| PRT3 | PartC | ghi | bbb |
--------------------------------------------------------------
Table 2
---------------------------------------------------------------
| Cat_num | Cat_name | SDsc1 | SDsc2 |
---------------------------------------------------------------
| CAT1 | CatalogA | abc | xyz |
| CAT2 | CatalogB | def | aaa |
| CAT3 | CatalogC | ghi | bbb |
---------------------------------------------------------------
Now, I want to append them to get this :
Table 3
-----------------------------------------------------------------------------------------------------------------------------
| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
-----------------------------------------------------------------------------------------------------------------------------
| PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
----------------------------------------------------------------------------------------------------------------------------
The blanks in Table 3 are , well ,blank.
Now can it be done or not?
Awaiting your replies,
Regards,
Shwetabh
My browser isn''t displaying your Table 3 very well so I''m not quite
certain which data is going into which column. In general you can merge
tables like this using UNION:
SELECT part_num, prt_name, desc1, ...
FROM Table1
UNION ALL
SELECT NULL, NULL, cat_name, ...
FROM Table2 ;
Each SELECT list in the UNION has to have the same number of columns
and each column has to be made up of compatible datatypes. In your case
it seems like the big question is what will be the key of Table3? It
isn''t clear to me whether it has a key at all.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
Shwetabh (sh**********@gmail.com) writes:Table 3
-------------------------------------------------------------------------- ---------------------------------------------------| Part_num | Prt_name | Desc1 | Desc2 | Cat_num | Cat_name |
SDsc1 | SDsc2 |
-------------------------------------------------------------------------- ---------------------------------------------------| PRT1 | PartA | abc | xyz |
|
| PRT2 | PartB | def | aaa |
|
| PRT3 | PartC | ghi | bbb |
|
| | | | | CAT1
| CatalogA | abc | xyz |
| | | | | CAT2
| CatalogB | def | aaa |
| | | | | CAT3
| CatalogC | ghi | bbb |
-------------------------------------------------------------------------- --------------------------------------------------
The blanks in Table 3 are , well ,blank.
Now can it be done or not?
Judging from the sample data you posted, what you want is
SELECT a.Part_num, a.Prt_name, a.Desc1, a.Desc2, b.Cat_num,
b.Cat_name, b.SDsc1, b.SDcs2
FROM table1 a
JOIN table2 b ON a.Desc1 = b.SDsc1
AND b.Desc2 = b.SDcs2
But I cannot say that it make much sense to join over a description column.
Maybe you need to consider a little more what you are actually looking
for and what you want to achieve.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Hi,
it seems Table 3 got pretty messed up.
So I will give the schema definations of the tables here:
Table 1:
CREATE TABLE TABLE1
(
PART_NUM varchar(10) primary key,
PRT_NAME VARCHAR(10),
DESC1 VARCHAR(20),
DESC2 VARCHAR(20)
)
Table 2:
CREATE TABLE TABLE2
(
PART_NUM varchar(10) primary key,
CAT_NUM VARCHAR(10),
CAT_NAME VARCHAR(10),
SDESC1 VARCHAR(20),
SDESC2 VARCHAR(20)
)
Now the resultant table should have the following schema:
CREATE TABLE TABLE3
(
PART_NUM varchar(10) primary key,
PRT_NAME VARCHAR(10),
DESC1 VARCHAR(20),
DESC2 VARCHAR(20),
CAT_NUM VARCHAR(10),
CAT_NAME VARCHAR(10),
SDESC1 VARCHAR(20),
SDESC2 VARCHAR(20)
)
This schema will be created programmatically.
Now my question is, if it is possible, how can I
insert records from table1 and table2 in table3?
I hope I have now made the things clearer.
Awaiting your reply,
Regards,
Shwetabh
这篇关于附表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
