×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Cobine tables' definition

Cobine tables' definition

Cobine tables' definition

(OP)
Hello all,

I have (what i hope to be) a simple question.

I received two tables from the same system that were used in different areas. These systems were not connected so, along the time, the tables definitions were independently changed. In simple words, Table_I has fields A, B, C, D and E and Table_II has fields A, B, D, F and H (actually each table has more than 150 fields, almost half of then not corresponding in the other table, thats why i'd like to avoid a too manual field creation process).

I would like to combine these two tables, keeping field aligment, i mean, the resulting field Table_III.A should have all records from Table_I.A and all records from Table_II.A, the resulting field Table_III.C the records from Table_I.C, plus the default value (or null) for the records from Table_II, which does not have C. And so on.

Hope that the explanation is clear enough. Pls, let me know if further details are needed.

BTW, if this is not the correct forum for this question, pls forgive me and point me in the correct direction.

Thank you in advance for any assistance.

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
 

RE: Cobine tables' definition

Use the DTS wizard with this query.

CODE

SELECT T1.A, T1.B, T1.C, T1.D, T1.E,
       T2.F, T2.H
FROM Table_1 T1
JOIN Table_2 T2 ON T2.A = T1.A

With this query the DTS wizard will create a new table with columns A, B, C, D, E, F, and H.  The values in columns A, B, C, D, and E will be from Table_1; the values in columns F and H will be from Table_2.

The new table will have all of the rows common to both tables.  Rows which exist in only one table will not be in the new table.

This is based on the assumption that column A is the primary key column for both tables.


You may add the rows which only exist in one table with queries like these.

Rows unique to Table_1 --

CODE

SELECT T1.A, T1.B, T1.C, T1.D, T1.E,
       T2.F, T2.H
FROM Table_1 T1
LEFT JOIN Table_2 T2 ON T2.A = T1.A
WHERE T2.A IS NULL
Rows unique to Table_2 --

CODE

SELECT T2.A, T2.B, T2.C, T1.D, T1.E,
       T2.F, T2.H
FROM Table_2 T2
LEFT JOIN Table_1 T1 ON T1.A = T2.A
WHERE T1.A IS NULL
In the second query note that column A is from Table_2 and the FROM clause mentions Table_2 JOINed with Table_1.

Also note that columns B and C are assumed to be defined in both Table_1 and Table_2 so the values come from the table which has the extra rows.

Doing this will add the rows which exist in only one table.

The values for the special columns will be filled with NULL for the rows unique to one table.  For example, a row found only in Table_2 will have the value NULL for columns D and E because that row comes from Table_2 which does not have columns D and E.


This procedure uses the values from Table_1 for the columns common to both tables.  In this example the common columns are A, D, and E.

If you wish to plug a default value for some columns this can be done by UPDATEing the new table columns where the value is NULL with the default value.

For example,

CODE

UPDATE Table_3 SET
  D = 'YourDefaultForColumnD',
WHERE D IS NULL

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close