By David Crowther
Question:
How can you copy records from one SQL Table and Insert into another SQL Table, where the source table shares an ID with another table?
Answer:
If you store your spatial data in a SQL database, it will be good to learn several common SQL queries to manage that data correctly.
In a previous FAQ we looked at how to copy records from Table A (VE_SKETCH_LAYER_GRAPHICS) and insert into a new Table – Table B (Davids_Planning).
This is the syntax –
select *
INTO DATABASENAME.dbo.NEWTABLE
From TABLEA
where FIELD = 8
Now using the table and field names for the actual data –
select *
INTO GeoStore_BDC_Live.dbo.Davids_Planning
From VE_SKETCH_LAYER_GRAPHICS
where sketch_layer_id = 8
and the new Table (Davids_Planning) is created into the Database. Note that the table is created using ALL the FIELDS (*) from the source table.
INSERT INTO USING JOIN –
In this example we have some records from Table A (VE_SKETCH_LAYER_GRAPHICS) that we wish to copy and insert into a new Table – Table B (Davids_Planning), but only where a value in Table A match’s values in a second table – Table C ( VE_SKETCH_LAYERS)
TABLE A = VE_SKETCH_LAYER_GRAPHICS
TABLE C = VE_SKETCH_LAYERS
Note -these two tables share the value Sketch_Layer_ID
The records that we wish to copy out from Table A, will be those that have the same SKETCH_LAYER_ID, as any record in Table C….. but only where the ROLE_ID = 16 or 27 in Table C.
Note – Table A does not contain the ROLE_ID value.
How else could we do this?
1 - If the ROLE_ID value was already in Table A (VE_SKETCH_LAYER_GRAPHICS) we wouldn’t need to use Table C as a JOIN.
2 – We could list all the Sketch_Layer_ID values that are associated to ROLE 16 or 27, and then use that to query out the records from Table A and produce a full list of Sketch_Layer_ID values. However, as below there are 197 records, associated to ROLE 16 and 27, and so that query would be too long to write.
So, the best option is to use Table C (VE_SKETCH_LAYERS) as a JOIN to only select the Sketch_Layer_ID’s associated with ROLE_ID 16 and 27.
This is the syntax -
select Fields from TableC, Fields from TableA
INTO DATABASENAME.dbo.NEWTABLENAME
From TableC, TableA
where ROLE_ID in (16,27)
And TableA.sketch_layer_id = TableC.sketch_layer_id
This is the syntax using our table and field names -
select ROLE_ID,LAYER_NAME,VE_SKETCH_LAYER_GRAPHICS.GEOM,VE_SKETCH_LAYER_GRAPHICS.sketch_layer_id
INTO GeoStore_BDC_Live.dbo. Davids_Planning
From VE_SKETCH_LAYERS,VE_SKETCH_LAYER_GRAPHICS
where ROLE_ID in (16,27)
And VE_SKETCH_LAYER_GRAPHICS.sketch_layer_id = VE_SKETCH_LAYERS.sketch_layer_id
If we run this query, we can see it returns 2721 records
and the new Table (Davids_Planning) is created into the Database. Note that the table is created using only the fields that were selected from each of the 2 source tables.
If we view the Spatial results, we can see that the geometry has also been inserted.