How to get values from one table column, concatenate them with a string and insert them in another table?

Question

How to:

1. Get values from table1.column1 (e.g. abc)

   table1.column1=abc

2. Concatenate them with certain fixed strings, e.g.

   xxx yyy zzz

3. Insert the results as separate rows in table2.column2. The final result should be rows with values like this:

   table2.column2=abc_xxx table2.column2=abc_yyy table2.column2=abc_zzz

(table2 has a connecting column indicating to which ID the table2.column2 record corresponds in case this matters)

4. Repeat this process for all records in table1.column1 which have table1.item_id > 100

EDIT: For certain convenience I would like the final result rows sequence to look like:

source1_xxx source1_yyy source1_zzz source2_xxx source2_yyy source2_zzz

and not like:

source1_xxx source2_xxx source1_yyy source2_yyy source1_zzz source2_zzz

Answer 1

If I understand you correctly, you want N (e.g. 3) entries for every existing row in Table1. If so, you can CROSS JOIN Table1 to a projection of the values, like so:

INSERT INTO Table2(column2)
    SELECT CONCAT(t1.column1, '_', x.col)
    FROM Table1 t1
    CROSS JOIN 
    (SELECT 'xxx' AS col
     UNION
     SELECT 'yyy'
     UNION 
     SELECT 'zzz') x;

SqlFiddle here

Edit

The query was updated to be cognaisant of the ordering and filtering requirements as well:

INSERT INTO Table2(column2)
    SELECT CONCAT(t1.column1, '_', x.col)
    FROM Table1 t1
    CROSS JOIN 
    (SELECT 'xxx' AS col
     UNION
     SELECT 'yyy'
     UNION 
     SELECT 'zzz') x
    WHERE t1.ID > 100
    ORDER BY t1.column1 ASC, x.col ASC;

With an updated SqlFiddle