Remove requirement for identity fid column in MSSQL tables.

[jpalmer]

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.

It should be possible to configure ogr2ogr 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?

[tamas]

Fixed in trunk(r25860) and branch-1.9 (r25861)