4

Closed

Import into existing table

description

There should be a way to import records to an existing table.
 
Scenario provided by user jmace:
"I have an application where users want to be able to upload and edit shapefile data. I am trying to determine what would be the best way to do this. The shapefile will relate to other data stored in a SQL Server 2000 database. Most of the DBAs that I talk to cringe when I tell them that every time you upload a shapefile it creates its own unique table. This application could have many users uploading shapefiles, so the number of tables could grow very quickly. "
 
Additionally, it should provide a way to insert default values for other fields, eg. "username".
Closed Jul 30, 2007 at 6:41 PM by rstuven

comments

SharpGIS wrote Apr 26, 2007 at 4:11 PM

If multiple users can upload multiple shapefiles, how do you enforce the table schema? Multiple shapefiles will most likely have multiple table schemas.

wrote May 1, 2007 at 1:12 AM

wrote May 7, 2007 at 8:07 PM

wrote Jul 9, 2007 at 5:36 PM

legion80 wrote Jul 23, 2007 at 3:50 PM

In my case I have shapefiles all of which conform to the same schema, but represent different areas of the world. For example, I'd like to load the street data for Illinois, and then later append the street data for Florida. (From a source such as TIGER)

wrote Jul 30, 2007 at 2:27 AM

Resolved with changeset 25155.

rstuven wrote Jul 30, 2007 at 2:49 AM

Added append_rows argument to msscmd.exe command-line utility and ST.ImportFromShapefile stored procedure. If append_rows is false, creates a new target table. If append_rows is true, imports into an existing table (source and target schemas must be compatible). Default value is false.

Import process is transactional, so in case of failing it's fully rollbacked.

Any other transformation should be implemented importing into a new table and writing a custom SQL command:{{INSERT INTO <existing_table> SELECT <custom_column_transformations> FROM <new_imported_table>}}

rstuven wrote Jul 30, 2007 at 6:41 PM

** Closed by rstuven 7/29/2007 7:27 PM

rstuven wrote Jul 30, 2007 at 6:41 PM

wrote Jul 30, 2007 at 6:41 PM

wrote Jul 30, 2007 at 6:41 PM

wrote Feb 14, 2013 at 5:56 PM

wrote May 16, 2013 at 8:43 AM