If you want to get really fancy, you can add a trigger that updates a third table which maps old primary keys to newly generated primary keys. A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. AS SELECT" statement creates and populates a database table based on the results of a SELECT statement. Inserting NULL primary keys into src causes them to be given auto-generated values.Ī "CREATE TABLE. This example works because the table "tmp" has no primary key constraint, but "src" does. To only duplicate a desired row, simply add a WHERE clause to the first line. The above example duplicates all rows of the table "src". To duplicate the rows of "src", use the following SQL in SQLite3: CREATE TEMPORARY TABLE tmp AS SELECT * FROM src In this example I assume that there is an existing, populated, table called "src" with an INTEGER PRIMARY KEY called "id", as well as several other columns. The trick is to create a temporary table using the "CREATE TABLE AS" syntax. #> row Sepal.Length Sepal.Width Petal.Length Petal.Width SpeciesĬreated on by the reprex package (v0.3.This can be done using * syntax without having to know the schema of the table (other than the name of the primary key). Insert_and_ignore_duplicates(con, iris_new) # iris_new has rows 101-250, so 50 are duplicated #> SQL insert or ignore into final select * from stage #> Warning: Factors converted to character
Insert_and_ignore_duplicates(con, iris) # iris has rows 100-150 #> SQL create unique index identity_check on final (row) #> Warning: Closing open result set, pending rows #> The following objects are masked from 'package:base': #> The following objects are masked from 'package:stats': Thanks wrapped up the key commands into a little function, insert_and_ignore_duplicates(): library(DBI) If (nrow(check)= nrow(iris)) print("all is well :)") # make sure by reading the final & comparing row count with original iris # try for the second time - this should have no effect "insert or ignore into final select * from stage ") "create unique index identity_check on final (row) ") on conflict do nothing these differ in detail but the principle is the sameĬon <- dbConnect(RSQLite::SQLite(), ":memory:") the contents of the stage layer are flipped over to final via a SQL command the exact formulation will depend on your dialect - sqlite has insert or ignore, while other dialects may have insert.one that will be final, in my case called final this should have a constraint - in my case via unique index called identity_check on field row in final table.one that will accept your R object, in my case called stage this should have no constraints (it should accept everything as it comes) and should be wiped clean before regular processing.In short, I'm looking for something like the following to add 100 new rows - and to ignore the first 50 with that are duplicates (because of the duplicate row ID): library(DBI)Īn option you may consider - though it may seem like an overkill in your use case - is to use the BI concept known as stage table. If you use the INSERT IGNORE statement, the rows with invalid data that cause the error are ignored and the rows with valid data are inserted into the tableīut, neither DBI::dbWriteTable() and DBI::dbAppendTable() (or any other function from the DBI package) appear to support it. It seems like there is a way to do this in SQLite using an INSERT IGNORE command/part of a query: However, my question is: Is there a way to only add to a table rows that are unique? Here - correctly - none of the rows were inserted, because all were duplicates. #> Error: UNIQUE constraint failed: iris.row If you manually create a table using DBI::dbSendQuery(), you can specify a primary key my understanding is that this makes it so that duplicate rows cannot be added (by adding as a constraint that there are no duplicated primary keys): library(DBI) One challenge I've encountered concerns inserting duplicate rows.Īs in the following reprex, it's easy to (advertently or not) add duplicate rows: library(DBI) I am interested in creating and populating a SQLite database via R.
0 kommentar(er)
