• Blog posts

    Bio

    By David Crowther

    SQL_-_Join_and_Insert_Into_-_1.JPG

    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.

    SQL_-_Join_and_Insert_Into_-_2.JPG

    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

    SQL_-_Join_and_Insert_Into_-_3.JPG

    TABLE C = VE_SKETCH_LAYERS

    SQL_-_Join_and_Insert_Into_-_4.JPG

    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.

    SQL_-_Join_and_Insert_Into_-_5.JPG

    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

    SQL_-_Join_and_Insert_Into_-_6.JPG

    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.

    SQL_-_Join_and_Insert_Into_-_7.JPG

    If we view the Spatial results, we can see that the geometry has also been inserted.

    SQL_-_Join_and_Insert_Into_-_8.JPG

Comments

0 comments

Please sign in to leave a comment.