9.3 Concatenate members of a sequence
Concatenate members of a sequence into a string.
Table A and table B have same structure. We will update a record of table A with the corresponding record of table B when the latter’s primary key matches, or add the table B’s record to table A when table B’s primary key does not exist in table A.
Table A:
| ID | Amount | … |
|---|---|---|
| 1 | 3063.0 | … |
| 2 | 3868.6 | … |
| 4 | 2713.5 | … |
| … | … | … |
Table B:
| ID | Amount | … |
|---|---|---|
| 1 | 3063.0 | … |
| 2 | 4507.0 | … |
| 3 | 2713.5 | … |
| … | … | … |
SPL offers A.concat(d) function to concatenate members of a sequence using separator d and return result as a string. Use the comma to concatenate members when @c option is present.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE k where k.TABLE_NAME=‘B’”) |
| 3 | =pks=A2.(COLUMN_NAME) |
| 4 | =A1.query@x(“select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME=‘B’”) |
| 5 | =columns=A4.(COLUMN_NAME) |
| 6 | =“MERGE INTO A as t USING B as s ON “+pks.(“t.”+~+”=s.”+~).concat("and “)+” WHEN MATCHED THEN UPDATE SET “+(columns\pks).(“t.” + ~ +”=s." + ~).concat@c()+"WHEN NOT MATCHED THEN INSERT VALUES(“+columns.(“s.”+ ~).concat@c()+”)" |
| 7 | =A1.excute(A6) |
A1 Connect to the database.
A2 Get primary key from the system table. Different types of databases have different ways of getting the primary key, and we take MSSQL as an example.
A3 Define variable pks, which is a sequence of names of primary key fields.
A4 Query all columns of the table.
A5 Define variable columns, which is a sequence of column names.
A6 Dynamically piece together a MERGE INTO statement, where A.concat() function concatenates members of a sequence and returns result as a string.
A7 Execute A6’s MERGE INTO statement.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
