SQL - Join and Insert Into

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.