Hi,

Use INSERT INTO SELECT ..

e.g.

INSERT INTO MAILSERVERS(NAME, SERVER, REQUIREAUTH, SMTPPORT)
SELECT A.IP_ADDRESS, A.IP_ADDRESS, 0, 25
FROM ip_addresses A

Look also at „MERGE” statement which is much more powerfull then simple insert statement

Pozdrawiam,
Karol Bieniaszewski

Od: 'Vaughan Wickham' vw@... [firebird-support]
Wysłano: piątek, 31 sierpnia 2018 06:01
Do: firebird-support@yahoogroups.com
Temat: [firebird-support] Need help with Insert Query

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

[Non-text portions of this message have been removed]