[gdal-dev] Loading data in MSSQL using ogr2ogr

[Jeremy Palmer]

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.

#####################################################################################