Select Into Statement with modified value and wildcard

user_12286658 0
Viewed 158

Is it possible to make a INSERT SELECT Statement for same table with modified values and wildcard for the rest colums?

I succeeded in getting a select insert statement with modified values. but not additionally with a wildcard. I would like to avoid having to name every single column.

V_ are variables :)

that the way i want.

INSERT INTO Table_1 (
        T1.ID,
        T1.NAME,
        T1.SITE,
        T1.*) 
SELECT          T1.V_DE_SITE,
        T1.V_NAME,
        T1.V_SITE,
        T1.*
FROM Table_1 AS T1 WHERE T1.ID = V_SO_SITE;

the following is possible i did it, but without wildcard! :( the table has some more columns

INSERT INTO Table_1 (
        ID,
        NAME,
        SITE) 
SELECT          V_DE_SITE,
        V_NAME,
        V_SITE
FROM Table_1 WHERE ID = V_SO_SITE;

Edit more info:

Is it possible to write: 

Insert into tablexyz  (id, name, * ) select 3, 'hello', * from tablexyz where id = 100;

i change id and name for new data set and the * copy the Rest of colums 1:1 to new id.... thats all i need. I will not write down all the colums in statement.

db2 select sql
2 Answers

You can't use wildcard in this case. But you may generate a template of such a statement like this:

SELECT 
  'INSERT INTO "'||TABSCHEMA||'"."'||TABNAME||'"'
--||' ('||LISTAGG('"'||COLNAME||'"', ', ') WITHIN GROUP (ORDER BY COLNO) ||')'
||x'0a'||'SELECT ' ||LISTAGG('"'||COLNAME||'"', x'0a'||', ') WITHIN GROUP (ORDER BY COLNO)
||x'0a'||'FROM "'||TABSCHEMA||'"."'||TABNAME||'";' STMT
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA='DB2ADMIN' AND TABNAME='EMPLOYEE'
GROUP BY TABSCHEMA, TABNAME;

The result is:

STMT
----
INSERT INTO "DB2ADMIN"."EMPLOYEE"
SELECT "EMPNO"
, "FIRSTNME"
, "MIDINIT"
, "LASTNAME"
, "WORKDEPT"
, "PHONENO"
, "HIREDATE"
, "JOB"
, "EDLEVEL"
, "SEX"
, "BIRTHDATE"
, "SALARY"
, "BONUS"
, "COMM"
FROM "DB2ADMIN"."EMPLOYEE";

Now you may specify expressions for needed columns in the select list accordingly.
If you uncomment the commented out line, all the table's columns in the insert are specified. You may omit such a specification if you want.

user_104182640

At your initial wanting insert statement, it has "where id = 100". It means to me that you want to change one specific row. If so, it may be possible to use update statement instead. Here is a sample.

#!/bin/sh

db2 -v "drop database db1"
db2 -v "create database db1"
db2 -v "connect to db1"
db2 -v "create table t1 (c1 int, c2 char(10), c3 char(10), c4 char(10), c5 char(10))"
db2 -v "insert into t1 values ( 1, 'aaa', 'AAA', 'aaaa', 'AAAA')"
db2 -v "insert into t1 values ( 2, 'bbb', 'BBB', 'bbbb', 'BBBB')"
db2 -v "insert into t1 values ( 3, 'ccc', 'CCC', 'cccc', 'CCCC')"
db2 -v "select * from t1"
db2 -v "update t1 set c1=4, c2='ddd' where c1=3"
db2 -v "select * from t1"
db2 -v "terminate"

The first select * from t1 shows below which is current data in the table t1.

C1          C2         C3         C4         C5
----------- ---------- ---------- ---------- ----------
          1 aaa        AAA        aaaa       AAAA
          2 bbb        BBB        bbbb       BBBB
          3 ccc        CCC        cccc       CCCC

And second one shows below after the update statement:

C1          C2         C3         C4         C5
----------- ---------- ---------- ---------- ----------
          1 aaa        AAA        aaaa       AAAA
          2 bbb        BBB        bbbb       BBBB
          4 ddd        CCC        cccc       CCCC

As above, we could change C1=4 and C2='ddd' but keep rest of columns for the row C1=3 was.

Please kindly ignore, if I missunderstand your requiment.

Hope this helps.

user_119340680
Related