[Qgis-user] merge multiple spatialite tables into one

[Bo Victor Thomsen]

To Etienne -

If you don't do it "The SQL way" you'll probably get into trouble with 
duplicate key values for the primary key index in your spatialite tables.

I assume:

  * That your tables - let's call them TABLE_A and TABLE_B has exactly
    the same structure
  * All tables has a integer primary key column - lets call it OGC_FID
  * You want to add the rows of TABLE_B to TABLE_A

The you can do the following:

 1. Open the dbmanager in QGis (*Menu Database* --> *DB Manager* --> *DB
    manager*). QGIS shows a dialog-window "DB Manager"
 2. *Double-click* on the spatialite database you want to work with in
    the selection tree (left side of the "DB Manager" dialog) and push
    the *F2* on the keyboard. QGis shows another dialog-window "SQL
    window - /database name/"
 3. Write "*select max (OGC_FID) from TABLE_A"* in the "query" window
    and push the *F5* button to execute the query. Remember the result
    value.
 4. Write "*update* *TABLE_B set OGC_FID = OGC_FID + */maxA/*+ 1" *in
    the "query" window and push the *F5* button to execute the query*.
    First *replace /maxA /with the found value from step 3. This query
    will guarantee, that there is no identical OGC_FID values in the two
    tables.
 5. Write *"insert into TABLE_A select * from TABLE_B"* in the "query"
    window and push the *F5* button to execute the query. This query
    will copy the rows from TABLE_B and append them to TABLE_A.
 6. Repeat steps 3 - 5 and replace TABLE_B with the names of the other
    tables you want to add to TABLE_A.

Tip: Write all the SQL commands into a text-editor (ex. notepad) and 
copy-paste them one-by-one into the query window. It can save you for a 
lot of grief.

Regards
Bo Victor Thomsen
Aeastas-GIS
Denmark



Den 27-08-2012 23:44, Etienne Tourigny skrev:
> Hi all,
>
> I have a number of spatial tables in spatialite DB, each with similar
> data, that I want to merge into a single table. I don't want to join
> them, but have a single table/view with data from all tables.
>
> I know this can be achieved through sql commands, but is there an easy
> (GUI) way to insert content from one table into another?
>
> Thanks,
> Etienne
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20120828/67effeb4/attachment.html>