How to do a "rbind" in tables from a SQL base from R?

Navigation
5

I would like to "merge" two tables from a SQLite database from the R - and save this into a new table within the same database. Here is a minimum reproducible code: 

    install.packages("sqldf",dependencies=T)
    install.packages("RSQLite",dependencies=T)
    library(sqldf)

    A <- data.frame(var1 = 1:5, var2=55:59)
    B <- data.frame(var1 = 11:15, var2=155:159)

    drv <- dbDriver("SQLite") 
    con <- dbConnect(drv, "basequalquer.db") 
    dbWriteTable(con, "TabelaA", A) 
    dbWriteTable(con, "TabelaB", B)

The result I want is this one: 

    dbGetQuery(con, 'SELECT * FROM TabelaA UNION ALL SELECT * FROM TabelaB' )

       row_names var1 var2
    1          1    1   55
    2          2    2   56
    3          3    3   57
    4          4    4   58
    5          5    5   59
    6          1   11  155
    7          2   12  156
    8          3   13  157
    9          4   14  158
    10         5   15  159

I can do a query to get it, but I do not know how to save it directly in the database (ie without having to save it in a data.frame and then do dbWriteTable)

    
asked by anonymous 18.09.2014 / 02:27

2 answers

4

This should not be the only way to do this, but try putting the CREATE TABLE command in the query itself: 

dbGetQuery(con, 'CREATE TABLE TabelaC AS
                 SELECT * FROM TabelaA 
                 UNION ALL 
                 SELECT * FROM TabelaB' )

Testing if the table was created: 

dbGetQuery(con, "select * from Tabelac")
   row_names var1 var2
1          1    1   55
2          2    2   56
3          3    3   57
4          4    4   58
5          5    5   59
6          1   11  155
7          2   12  156
8          3   13  157
9          4   14  158
10         5   15  159
    
18.09.2014 / 02:45
6

Let me add my 2 cents: Carlos's solution is correct, but in this solution you will copy each entry of the two tables into a new table and they will no longer be related, that is, any change in the original tables will not be reflected in this new table created in the merge.

One feature that SQLite3 and other database management systems have is VIEW ( link ) that allows you to create the same merge as before, except that the table is sensitive to changes in the original table. If you insert or remove records from TableA or TableB, when querying VIEW the changes will be present there as well. 

library(sqldf)

   A <- data.frame(var1 = 1:5, var2=55:59)
   B <- data.frame(var1 = 11:15, var2=155:159)

   drv <- dbDriver("SQLite") 
   con <- dbConnect(drv, "basequalquer.db") 
   dbWriteTable(con, "TabelaA", A) 
   dbWriteTable(con, "TabelaB", B) 

   ## Criando a VIEW
   dbGetQuery(con, 'CREATE VIEW
                    merge
                 AS
                    SELECT * FROM TabelaA 
                        UNION ALL 
                    SELECT * FROM TabelaB')

   ## Fazendo uma consulta na VIEW
   dbGetQuery(con, 'SELECT * FROM merge')

 row_names var1 var2
1          1    1   55
2          2    2   56
3          3    3   57
4          4    4   58
5          5    5   59
6          1   11  155
7          2   12  156
8          3   13  157
9          4   14  158
10         5   15  159

   ## Removendo uma linha da TabelaA
   dbGetQuery(con, 'DELETE FROM TabelaA WHERE row_names = 3')

   ## Consultando a VIEW novamente
   ## veja que o registro sumiu da VIEW também!
   dbGetQuery(con, 'SELECT * FROM merge')

 row_names var1 var2
1         1    1   55
2         2    2   56
3         4    4   58
4         5    5   59
5         1   11  155
6         2   12  156
7         3   13  157
8         4   14  158
9         5   15  159
    
18.09.2014 / 06:44
Fixed-size table cell Select elements in hierarchy with Javascript