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

Shwetabh wrote:
> 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 looks like you'll want something like this:

INSERT INTO Table3
(part_num, prt_name, desc1, desc2,
cat_num, cat_name, sdesc1, sdesc2)
SELECT COALESCE(T1.part_num, T2.part_num),
T1.prt_name, T1.desc1, T1.desc2,
T2.cat_num, T2.cat_name, T2.sdesc1, T2.sdesc2
FROM Table1 AS T1
FULL JOIN Table2 AS T2
ON T1.part_num = T2.part_num ;

It still seems at least questionable whether Table3 or even Table2
represent "good" designs but as I only have your column names to go on
there isn't much point in me speculating about that.

--
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]