SQL – Insert Records

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.