Cobine tables' definition

[4N6Mstr]

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

 

[rac2]

Use the DTS wizard with this query.

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

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

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,

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