Posted by David Portas on 03/08/06 12:09

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/ms130214(en-US,SQL.90).aspx
--

[Back to original message]