How to create multiple copies of a data row while applying data from another table in SQL

Question

Environment: INFORMIX 9.50C1, AIX 6.1

The closest analogy to what I am trying to do is a Mail Merge. Here is the scenario:

One table (CLIENTS) has a large number of columns (72 columns). The table contains client data, and the columns represent various attributes of the client.

CLIENT_ID     RPQ_ID    Attrib2     Attrib3 ... Attrib71

The unique key on the table is the client id: CLIENT_ID, which is a 12-character alphanumeric field. All other columns do allow duplicates.

One row in the CLIENTS table is set up as a template for a new set of clients.

CLIENT_ID     RPQ_ID    Attrib2     Attrib3 ... Attrib71
TEMPLATE_017  000000    London017   CLS12   ... 12

I have second table (TMP_IMPORT) that contains a list of new client ids with one additional non-unique attribute (RPQ ID). The import list has ~2000 rows.

CLIENT_ID     RPQ_ID
GPR3344       HG777
JND4111       JL888
JPS3172       JL888
PAP2171       JL888
...

The task is to perform the equivalent of a mail merge: add the 2000 new rows to the CLIENTS table, using the client_id and rpq_id values from TMP_IMPORT and all the other columns from the template row.

The expected result in table CLIENTS is

CLIENT_ID     RPQ_ID   Attrib2     Attrib3 ... Attrib71
TEMPLATE_017  000000   London017   CLS12   ... 12
GPR3344       HG777    London017   CLS12   ... 12
JND4111       JL888    London017   CLS12   ... 12
JPS3172       JL888    London017   CLS12   ... 12
PAP2171       JL888    London017   CLS12   ... 12
...

This is all taking place within a large corporation with very little flexibility as far as access control, execution rights, etc. The solution must use straight SQL, without shell scripts, procedures, ot third party libraries.

Also, please: no enumeration of the columns. One of the common solutions I've seen is to join the tables and specify the source of each data column, as in

select TMP_IMPORT.CLIENT_ID, 
         TMP_IMPORT.RPQ_ID, 
         CLIENTS.Attrib2, 
         CLIENTS.Attrib3, 
         ..., 
         CLIENTS.Attrib71
from TMP_IMPORT, CLIENTS ...

This would be reasonable for a table with just a few columns, but I hope someone has a more elegant solution.

Thank you!

Note that there wasn't formally a released 9.50 version; it was rebadged to 10.00 before release. There were (and still are) vestiges of the 9.5x versions in the system. Note too that Informix 10.00 is long out of service; you should be upgrading to 12.10 (or 11.70 at a pinch). — Jonathan Leffler, Aug 02 '13 at 03:51

score 1 · Answer 1 · answered Jul 27 '13 at 11:31

1

You seem to know the query that you want:

insert into clients(client_id, rpt_id, . . . , Attrib71)
    select ti.CLIENT_ID, ti.RPQ_ID, c.Attrib2, c.Attrib3, ..., CLIENTS.Attrib71
    from TMP_IMPORT ti cross join
         (select c.*
          from CLIENTS c
          where client_id = 'Template_017'
         ) c;

Your problem seems to be getting the list of column names. The easiest way to get the columns is the following:

select colname
from syscolumns c join
     systables t
     on c.tabid = t.tabid and t.tabname = 'CLIENTS'
order by colno;

In fact, you can do:

select colname || ', '

and then copy the results into your query editor for the column list for the insert (and remove the final comma). And then copy them into the appropriate place in the select list as well.

answered Jul 27 '13 at 11:31

Gordon Linoff

1,242,037
58
646
786

I like the idea of using system tables to get the column names. But this solution still requires a jump "outside", to an editor. Is there a way to create the query dynamically, rather than construct it in an editor. – user2624414 Jul 30 '13 at 20:27
@user2624414 . . . In most databases, the answer would be "yes". Informix does not have an equivalent to `group_concat()` or `listagg()`, so it is much harder in Informix. – Gordon Linoff Jul 30 '13 at 23:35
Informix stores table names in lower-case unless you are very firm with it (and you don't want to be that firm with it). See [Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)](http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separated) for how to create GROUP_CONCAT for Informix. – Jonathan Leffler Aug 02 '13 at 03:46

How to create multiple copies of a data row while applying data from another table in SQL

1 Answers1