"Without joins and set operators" is a pointless artificial restriction. Unless this is some sort of homework assignment, arbitrarily ruling out an entire set of solutions to your problem serves absolutely no purpose.
Your code sample
is using a join behind the scenes; you're just using an ancient and obsolete syntax for a
CROSS JOIN
. That is, since you haven't specified any conditions to relate the rows from the two tables,
every row from table A will be matched to
every row from table B, generating a cross-product of the two tables.
For example, if you have 10 rows in table A, and 100 rows in table B, you will end up with 1000 rows in table C.
If that is your intention, then you just need to update your syntax:
INSERT INTO TableC (col1, col2, col3, col4)
SELECT t1.col1, T1.col2, T2.col3, T2.col4
FROM tableA As t1 CROSS JOIN tableB As t2;
If your intention is
not to produce a cross-product, then you need to specify the joining conditions to match rows from table A to rows from table B. For example:
INSERT INTO TableC (col1, col2, col3, col4)
SELECT t1.col1, T1.col2, T2.col3, T2.col4
FROM tableA As t1 FULL OUTER JOIN tableB As t2
ON t1.pk = t2.pk;
In the case of an outer join, you will end up with
NULL
values when there is no matching row. For example, given:
TableA TableB
pk col1 pk col3
1 A 1 X
2 B 3 Y
you will end up with:
TableC
col1 col3
A X
B NULL
NULL Y
Therefore, you need to ensure that the columns in table C allow
NULL
values.
Beyond that, you haven't explained what you mean by
"getting failed". If you get an error, provide the full details of the error. If you're not getting the expected data, provide sample inputs, the expected output, and the actual output.