Most efficent way to produce new table from 2 tables

[Mimo]

Hi

I have two Tables like below;

Table1
F1 F2 F3
1 One A
2 Two B
3 Three C

and Table 2:

F1 F2 F3 F4
1 One A a
2 Two B b
3 Three C c
4 Four D d
5 Five E e


I would like to produce Table 3 as follows


F1 F2 F3 F4
1 One A a
2 Two B b
3 Three C c

Now in reality my tables are quite large.
Table 1 has 56 fields and over 650,000 records.
Table 2 has 60 fields and over 750,000 records.

My question isoes any one know the most efficient way(at runtime)
of achieving this - So I don't have to wait for hours?

Thanx

Mimo

 

[BruceM]

The distinction between Table2 and Table3 is unclear, nor is the meaning of
"produce Table 3". Without knowing something of your database's structure
it is impossible to suggest anything specific, but in general you would use
a query to combine tables. You would not create a new table with duplicate
information.
Tables rarely needs as many as 60 fields, so there is a possibility your
database is not properly normalized. Again, there is not enough information
to understand what you are trying to do, or with what recordsets you are
trying to do it.

 

[Ken Sheridan]

On the basis of your sample data what you appear to want is to return all
rows from Table 2 where there is a match on F1 in Table 1, which either of
the following will give you:

SELECT [Table 2].*
FROM [Table 2] INNER JOIN [Table 1]
ON [Table 2].F1 = [Table 1].F1;

Or:

SELECT *
FROM [Table 2]
WHERE EXISTS
(SELECT *
FROM [Table 1]
WHERE [Table 1].F1 = [Table 2].F1);

You can of course insert the result set of either of the above into an empty
Table 3 if you wish, but you'd only do that if you either want a snapshot of
the data at the time the insert is executed, or you are going to dispose of
tables 1 and 2 and keep table 3 only. If the latter then you don't need to
insert rows into a third table; you can simply delete the unmatched rows from
table 2:

DELETE *
FROM [Table 2]
WHERE NOT EXISTS
(SELECT *
FROM [Table 1]
WHERE [Table 1].F1 = [Table 2].F1);

It goes without saying that you should back up the tables before executing
any large scale set operations like the above.

I'd share BruceM's reservations regarding the number of columns in your
tables, however. I'd be surprised if you should not also be looking to
decompose the result table.

Ken Sheridan
Stafford, England