By David Crowther
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)
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.
The new Table (Davids_Planning) is created into the Database. Note that the table is created using ALL the FIELDS (*) from the source table.
If we view the Spatial results, we can see that the geometry has also been inserted.
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
This time the new table is created, but with only the fields we selected from the source table.