• EN
Question

insert into identity column option in SQL Server spatial writer

  • 14 November 2012
  • 3 replies
  • 49 views
B
Badge
G'day guys,

 

 

Just wondered if anyone could explain how the "insert into identity column" option in the format parameters of the SQL Server spatial writer works, please.

 

 

I want the writer to create a table with an Identity column called "Row_No" and populate it as the features flow into it, so I created an Identity column called "Row_No" in the writer, then set the "insert into identity column" option to 'yes'

 

but get the following error : "Cannot insert the value NULL into column 'row_no'" and nothing gets written to the table.

 

 

If I set the set the "insert into identity column" option to 'no' then it populates the "Row_No" field as I'd hoped but the log shows : "identity' of attribute `row_no' of feature type `test' is not supported for writing.  Attribute will be ignored by writer"

 

 

It's as if the yes and no option do the opposite of what I think they're going to do!

 

 

I couldn't see anything in the writer reference help so I'm confused! Help!

 

 

Cheers

 

 

Batesy

 

 

(I'm using FME Desktop 2012 (SQL Server edition) 32-bit)

3 replies

R
I hope i've understood what you're asking here...

 

 

Identity column is a specific SQL Server data type for integer columns. You cannot normally insert into this column, SQL Server generates the value for you. There is however an SQL Server override to allow you to populate it manually, if for instance you had a specific value range to use. So my reading of your entry is that you have created a column in fme which you want to be an "identity" but isn't  in  SQL Server terms. The "insert into identity column" option is expecting to find an identity column to override in the table definition.

 

 

see http://msdn.microsoft.com/en-us/library/ms186775.aspx
B
Badge
aaaaahh, I see, so the "insert into identity column" is actually the "SET IDENTITY_INSERT ON" command in SQL which allows you to override the Identity incremental count and input an integer from a feature.

 

 

Makes sense now!

 

 

Thanks Rob!

 

 

Batesy
sigtill
Rank
Badge +21
If I remember correctly you can also just create an attribute on the writer called IDENTITY, without giving it a value, and then it will be considered a IDENTITY column. I think you need to start with an empty or a DB that already has the IDENTITY column defined correctly
The real SigTill

Reply


Community Stats

28,823
Posts
109,858
Replies
36,638
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.