Re: Appending tables

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

.

Relevant Pages

  • Re: Can this be done using a Data adapter??
    ... values to table3. ... view and manually run the insert or update SQL commands. ... have the 1st column read only and put the table1 values there). ... have done up to now is i bring the data of table1 and table2 from the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Optimising the Query
    ... FROM TABLE1 TAB1 ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: Optimising the Query
    ... FROM TABLE1 TAB1 ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Optimising the Query
    ... FROM TABLE1 TAB1 ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: Appending tables
    ... So I will give the schema definations of the tables here: ... CREATE TABLE TABLE1 ... PART_NUM varcharprimary key, ... CREATE TABLE TABLE3 ...
    (comp.databases.ms-sqlserver)
Loading