Hello,

I haven’t used SQL for some time and I need some help with how to go about constructing the following query.

I have two tables:

1.       IP_addresses

2.       MailServers

I have an external table which is defined as follows:

Create Table ip_addresses

EXTERNAL File 'C:/ip_fixed_length.txt'

(

IP_ADDRESS CHAR(15) CHARACTER SET ASCII,

LINE_BREAK CHAR(2) CHARACTER SET ASCII DEFAULT x'0D0A'

);

I want to read the contents of the ip_addresses table and then add a new row to an existing table called: MAILSERVERS for each row (i.e. IP_ADDRESS) that exists in the IP_ADDRESSES table

The MAILSERVERS table (destination) contains the following fields:

NAME

SERVER

REQUIREAUTH

SMTPPORT

For each row that I add to the MAILSERVERS table I want to set the values as follows:

NAME = IP_ADDRESS (from ip_addresses)

SERVER = IP_ADDRESS (from ip_addresses)

REQUIREAUTH = 0

SMTPPORT = 25

I think I need to use the INSERT command, but all the examples that I can find assume that the fieldname (aka column) that is being referred to in the INSERT command exists in both tables.

However in my case the field names are different (and I can’t make them the same because there are two columns in the MailServers table)

I guess what I could do if it was the easiest way would be to modify my external table.

1.       Duplicate the IP_address column

2.       Rename the columns: NAME, SERVER

3.       Then use INSERT

4.       I figure there has to be a better way than this though

The problem that I have at the moment, is that I can’t work out how to do what I want (i.e. construct a query that is close to what I need that I can then tinker with to get the desired result)

Appreciate any advice / examples to give me a starting point

Thank you

Regards,