Hello Can any one help how can I do this simple SQ...

# exposed

[Attila Nagy]

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

[Endre Deak]

you need a few things there -

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

[Attila Nagy]

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 :

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'

[Endre Deak]

oh you want an insert … select statement

[Endre Deak]

not sure if it’s supported

[Endre Deak]

seems it is

[Endre Deak]

check this:
• https://github.com/JetBrains/Exposed/blob/a4bed0967958d11a0a1e0c67b1159ed7cbee6bc4[…]org/jetbrains/exposed/sql/tests/shared/dml/InsertSelectTests.kt

[Endre Deak]

and this: https://github.com/JetBrains/Exposed/blob/a4bed0967958d11a0a1e0c67b1159ed7cbee6bc4/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/Queries.kt#L162-L168

[Attila Nagy]

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).

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:

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))