• Blog posts

    Bio

    By David Crowther

    SQL___Insert_Records_-_1.JPG

    Question:

    How can you copy records from one SQL Table and Insert into another SQL 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 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).

    Here is Table A (VE_SKETCH_LAYER_GRAPHICS)

    SQL___Insert_Records_-_2.JPG

    Each Record has:

    • a unique ID (SKETCH_LAYER_GRAPHICS_ID)
    • a value defining the layer that it is associated to (SKETCH_LAYER_ID)
    • a Geometry field (GEOM)
    • and other values that define how that geometry is styled (STROKE_COLOUR, FILL_COOLOUR etc…)

    INSERT INTO NEW TABLE –

    In the first example we will select out all the records from Table A, where their SKETCH_LAYER_ID = 8 and insert them into a NewTable (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

    If we run this query, we can see it returns 11 records.

    SQL___Insert_Records_-_3.JPG

    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___Insert_Records_-_4.JPG

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

    SQL___Insert_Records_-_5.JPG

    INSERT INTO and FILTER FIELDS –

    This time, instead of using the * to select all Fields from the source table, we can choose to create the new table from a selection of the source tables fields.

    This is the syntax –

    select Field1, Field2, Field3

    INTO DATABASENAME.dbo.NEWTABLE

    From TABLEA

    where FIELD = 8

    Now using the table and field names for the actual data –

    select SKETCH_LAYER_ID,ELEMENT_NAME,NOTE, GEOM

    INTO GeoStore_BDC_Live.dbo.Davids_Planning

    From VE_SKETCH_LAYER_GRAPHICS

    where sketch_layer_id = 8

    SQL___Insert_Records_-_6.JPG

    This time the new table is created, but with only the fields we selected from the source table.

    SQL___Insert_Records_-_7.JPG

Comments

0 comments

Please sign in to leave a comment.