https://kotlinlang.org logo
#exposed
Title
# exposed
a

Attila Nagy

11/15/2021, 8:46 PM
Hello Can any one help how can I do this simple SQL insert in Exposed INSERT into Table 1(c1,c2,c3) Select c1,c2,c3 from table2
e

Endre Deak

11/15/2021, 8:52 PM
you need a few things there -
Copy code
object MyTable: Table("tableName") {
   // list your columns here
   val c1 = varchar("c1", 10)
   val c2 = ... 
}

// then somewhere in your code

transaction {
   MyTable.insert {
       it[c1] = "c1"
       it[c2] = "c2"
       ...
   }
}

// then retrieve
transaction {
   MyTable.select { MyTable.c1 eq "c1" }
}
Exposed has a really good documentation on how to do these things. https://github.com/JetBrains/Exposed
a

Attila Nagy

11/15/2021, 8:59 PM
Hello I did not wrote cleary what I would like to do I would like to insert data into table1 from table2 like this select :
Copy code
NSERT srowS07
(SSALID,
 SROWID,
 SRECNO,
 OITEMNO,
 ITEM,
 SUPL,
 BUYPR,
 DISCPC,
 ISORD,
 NAME,
 RSUM,
 UNITPR,
 RNO,
 RTYPE,
 VATCD,
 CREATED, NOTE, num, ORDNUM, ORIGUNITPR, ORGPRLISTQTY)
SELECT a.SSALID,
       NewRowNo = 1000 +
                  ROW_NUMBER() OVER (PARTITION BY b.SSALID ORDER BY a.SC_IB_CHASSISNO,SC_IB_INVOICEPERIODSTART),
       b.SRECNO,
       OITEMNO  = 'Merged',
       ITEM     = a.SC_IB_CHASSISNO,
       SUPL     = 'CONT',
       BUYPR    = 0,
       DISCPC   = 0,
       ISORD    = 0,
       Name     = 'JKSZ',-- AS ContType ,
       Rsum     = m.RSUM,
       UNITPR=m.UNITPR,

       RNO      =1000 + ROW_NUMBER() OVER (PARTITION BY a.SSALID ORDER BY a.SSALID),
       RTYPE    = 2,
       b.VATCD,
       b.CREATED,
        NOTE,
       num,
       ORDNUM,
       ORIGUNITPR,
       ORGPRLISTQTY


FROM dbo.RAMASINVRS07 a
         inner JOIN dbo.SROWS07 b ON b.SSALID = a.SSALID
    AND b.SROWID = a.SROWID
    AND a.SC_IB_CONTRACTTYPE LIKE '%EPC%'
    AND b.SRECNO = 0
    AND a.SC_IB_CONTRACTTYPEID IN (24, 25, 26, 27, 28, 31, 33, 34, 35, 36, 72, 73, 74, 75)
         INNER JOIN dbo.CorwS07 c ON b.VATCD = c.c3 AND c.CODAID = 'ALV'
e

Endre Deak

11/15/2021, 10:40 PM
oh you want an insert … select statement
not sure if it’s supported
seems it is
a

Attila Nagy

11/16/2021, 6:58 AM
Thanks I found this, but not so clear for me I am very new in Kotlin and Exposed If you want to use 
INSERT INTO ... SELECT
 SQL clause try Exposed analog 
Table.insert(Query)
.
Copy code
val substring = users.name.substring(1, 2)
cities.insert(users.slice(substring).selectAll().orderBy(users.id).limit(2))
By default it will try to insert into all non auto-increment 
Table
 columns in order they defined in Table instance. If you want to specify columns or change the order, provide list of columns as second parameter:
Copy code
val userCount = users.selectAll().count()
users.insert(users.slice(stringParam("Foo"), Random().castTo<String>(VarCharColumnType()).substring(1, 10)).selectAll(), columns = listOf(users.name, users.id))
37 Views