NEW STARTER - SELECTING DATA FROM A TABLE TO PUT IN ANOTHER

[JONBOY74]

Morning

Sorry if this question is a bit simply for the group, but I'm new to this.

I have 2 tables (TABLE A & TABLE B) both have the same columns etc but TABLE A has more rows (newer information).

What I'm trying to do is X-match the 2 tables and select all the rows that aren't in the table B but are in Table A

I've tried a number of select & where combination but I can't seem to work it out

Please Help

Many Thanks

Jon

 

[Tim1]

Hiya Jon,

The easiest way to do this is a sub-query, something like :

INSERT table_a (column_list)
SELECT b.column1, b.column2....
FROM table_b b
WHERE NOT EXISTS (SELECT 1
FROM table_a a
WHERE a.key_field1 = b.key_field1
AND a.key_field2 = b.key_field2)

Basically, what you need in place of key_field1, key_field2 etc. is whatever fields make each record unique. You shouldn't need to put the whole list of fields in there (i.e. every one in the table), just enough to make sure that each row can be uniquely identified (check indexes etc. for hints on unique keys).

This may be a little slow, but it should do what you require.

Tim