Adding/updating new data to table

Topics: User Forum
Jan 15, 2007 at 9:58 AM
Hi,

I am very new to using MsSqlSpatial, having only installed it and imported my first SHP file within the last 30 mins :) So far it looks very nice and I am getting the hang of it in no time at all!

There is a problem though, or at least I can't see the solution... When I import new data from a second SHP file, the original data in the table is dropped. Is it possible, instead of dropping the data, for me to append the new data to the table? Also, is it possible to update existing entries when my SHP file contains an entry with the same ID?

The reason I want to do this is that I have data for various parts of the UK, and I won't be loading it all into the database at once, but in stages as the data "arrives". Also, I will be receiving updates to the data every 3-6 months, so obviously I want to be able to upload this data without having to recreate the database from scratch each time.

Any help is greatly appreciated!

Thanks in advance,

Dylan
Coordinator
Jan 15, 2007 at 1:43 PM
Currently, the feature you need is not built-in. But I suggest you try the following:

Say your originally imported table is called streets. Import the "upgraded" shapefile to a different table, say streets_new. Also, we'll have a column for the original key, say original_id. Create an index for original_id in both tables. Ignore the oid column created by the import process, it will be useless in this scenario. Finally, we'll have two feature columns: name and class.

> Is it possible, instead of dropping the data,
> for me to append the new data to the table?

INSERT INTO streets(original_id, name, class)
SELECT original_id, name, class
FROM streets_new AS sn
WHERE NOT EXISTS
(
	SELECT s.original_id
	FROM streets AS s
	WHERE s.original_id = sn.original_id
)

> Also, is it possible to update existing
> entries when my SHP file contains an entry
> with the same ID?

UPDATE streets
SET name = sn.name,
class = sn.class
FROM streets AS s, streets_new AS sn
WHERE s.original_id = sn.original_id

Also, you may want to delete the rows are gone in the new version:

DELETE FROM streets
WHERE original_id IN 
(
	SELECT s.original_id
	FROM streets AS s 
	LEFT JOIN streets_new AS sn
	ON s.original_id = sn.original_id
	WHERE sn.original_id IS NULL
)

When you finish, drop the temporal table:
EXECUTE ST.DropGeometryTable('', 'streets_new')

Best regards,
Ricardo Stuven.
Coordinator
Jan 15, 2007 at 2:19 PM
Sorry, I forgot to add the_geom column in the INSERT and UPDATE statements.

Best regards,
Ricardo Stuven.
Jan 16, 2007 at 10:09 AM
Cheers. That would seem like the best currently available solution to the problem. I'll give it a whirl.