Thread
#exposed
    a

    Attila Nagy

    10 months ago
    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

    10 months ago
    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
    a

    Attila Nagy

    10 months ago
    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'
    e

    Endre Deak

    10 months ago
    oh you want an insert … select statement
    not sure if it’s supported
    seems it is
    a

    Attila Nagy

    10 months ago
    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))