[gdal-dev] Loading data in MSSQL using ogr2ogr

[Tamas Szekeres]

Jeremy,

This seems to be a reasonable requirement. You should create a ticket for
this issue at:
http://trac.osgeo.org/gdal/newticket

Best regards,

Tamas



2012/1/12 Jeremy Palmer <JPalmer at linz.govt.nz>

> Hi,
>
> I'm trying to load a CSV file into an existing MSSQL 2008 table using
> ogr2ogr:
>
> ogr2ogr -preserve_fid --debug on -append -skip
> failures -f MSSQLSpatial "MSSQL:server=xxxx\SQL_SERVER_2008;Integrated
> Security=true;database=xxxx;tables=test_csv_table_1(shape)" test1.vrt -nln
> test_csv
> _table_1 -s_srs epsg:4167
>
> The schema I've got is:
>
> CREATE TABLE [dbo].[test_csv_table_1](
>  [id] [int] NOT NULL,
>  [appellation] [text] NULL,
>  [affected_surveys] [text] NULL,
>  [parcel_intent] [text] NOT NULL,
>  [topology_type] [varchar](100) NOT NULL,
>  [statutory_actions] [text] NULL,
>  [land_district] [varchar](100) NOT NULL,
>  [titles] [text] NULL,
>  [survey_area] [numeric](20, 4) NULL,
>  [calc_area] [numeric](20, 4) NOT NULL,
>  [shape] [geometry] NULL,
> PRIMARY KEY CLUSTERED
> (
>  [id] ASC
> )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
>
> This currently fails. The reason being that the SQL generated by ogr2ogr
> is:
>
> SET IDENTITY_INSERT [dbo].[test_csv_table_1] ON;
>
> INSERT INTO [dbo].[test_csv_table_1] (shape, [id], [appellation],
> [affected_surveys], [parcel_intent], [topology_type], [statutory_actions],
> [land_district], [titles], [survey_area], [calc_area]) VALUES
> (geometry::STGeomFromText('POLYGON ((172.7825528
> -41.4211097333,172.78229796670001 -41.42127,172.7823694333
> -41.4212575333,172.7825528 -41.4211097333))',4167).MakeValid(), 3621203,
> 'Sec 8 SO 14436', 'SO 14436', 'DCDB', 'Primary', '[Referenced] Stopped Road
> to be Amalgamated [Tadmor Valley Road, Nelson] New Zealand Gazette 2009 p
> 186 Stopped and Amalgamated with Land in CT NL72/70', 'Nelson', '464435',
>            50.0000,              38.3921);SET IDENTITY_INSERT
> [dbo].[test_csv_table_1] OFF;
>
> The issue can be resolved if I change my table schema so the id field is
> an identity column, but I don't want to do this.
>
> Is it possible to configure (or fix a bug) so the "SET IDENTITY_INSERT"
> SQL is not executed if the -preserve_fid option is used and the existing
> table does not have an identity field?
>
> Cheers,
> Jeremy
>
> #####################################################################################
>
> This message contains information, which is confidential and may be
> subject to legal privilege.
> If you are not the intended recipient, you must not peruse, use,
> disseminate, distribute or copy this message.
> If you have received this message in error, please notify us immediately
> (Phone 0800 665 463 or info at linz.govt.nz) and destroy the original
> message.
> LINZ accepts no responsibility for changes to this email, or for any
> attachments, after its transmission from LINZ.
>
> Thank You.
>
>
> #####################################################################################
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20120112/34e5d0cd/attachment.html