Insert data from two tables to another one - join problem

[shyamal]

I am attempting to insert into a destination table from two source tables in the same database with certain criteria.

Table Names Table fields
SRC: Table1 - (fldintkey,fldint1,fldchar) - all null
SRC: Table2 - (fldintkey,fldint1,fldchar) - all null
Dst: Table_Dst -(fldintkey,fldint1,fldchar) - all null

Data: (fldintkey,fldint1,fldchar)
Table1: 1,1,'test'
Table2: 2,2,'test'
Table3: needs both rows from the above table where
fldchar = 'test'

The insert\join which does not work:

INSERT INTO Table3(FLDINTKEY,FLDINT1,FLDCHAR)
SELECT A.FLDINTKEY,A.FLDINT1,A.FLDCHAR,
B.FLDINTKEY,B.FLDINT1,B.FLDCHAR
FROM Table1 A,Table2 B
WHERE A.FLDCHAR = 'TEST'
AND B.FLDCHAR = 'TEST'

Thanks in advance.

 

[balves]

You cannot insert 6 fields (via the SELECT) into a 3 field table.

If there's no JOIN field between the 2 tables, then have 2 separate SELECTs with a UNION between them.

 

[shyamal]

Great, that works thanks a bunch!