4

Closed

Script as Data (INSERTs) - ignore IDENTITY

description

Whenever a column is identified as an IDENTITY column, that column should be excluded from the script. So an option to ignore any IDENTITY column(s) would be ideal.
 
"Script as Data (INSERTs)" is a great feature. However I use auto increment columns for PK. "Script as Data (INSERTs)" doesnt care when generating the script. So finally I have to edit the script by myself to get rid of the columns using autoincrement.
 
It would be great if the function would recognize them and would not include them into the script.
Closed Aug 29, 2012 at 7:14 PM by ErikEJ
In 3.1

comments

ErikEJ wrote Mar 9, 2012 at 10:56 AM

I think the script includes: SET IDENTITY_INSERT [Categories] ON; at the top, to allow inserts into IDENTITY columns !?

TomSch wrote Mar 9, 2012 at 5:12 PM

It only works if the IDENTITY doesn't already exists in the table.

ErikEJ wrote Mar 10, 2012 at 9:28 AM

True - but I am not sure I understand your use case - could you explain what you are trying to do? Would it be a solution to add an option to ignore the IDENTITY colmn when scripting the INSERTs ?

modao wrote Apr 15, 2012 at 9:05 AM

Hi Erik;

Your first assumption is correct. Whenever a column is identified as an IDENTITY column, that column should be excluded from the script. So an option to ignore any IDENTITY column(s) would be ideal,

ErikEJ wrote Apr 15, 2012 at 11:53 AM

Closing, feedback missing



** Closed by ErikEJ 4/15/2012 1:27 AM

ErikEJ wrote Apr 15, 2012 at 11:53 AM

Feedback provided!

modao wrote Apr 15, 2012 at 10:14 PM

Thanks for reopening this Erik. Altough I am not the original author of this request, it is most certainly valid, which is why I responded when I caught it. I do an identical thing with AUTOINCREMENT columns in ODBC and OLEDB INSERT scripts that I create for my customers. I consider ignoring IDENTITY/AUTOINCREMENT/SEQUENCE columns from INSERT statements to be "standard" practice.

Kind Regards - Dave

ErikEJ wrote Apr 16, 2012 at 6:25 AM

Thanks for the feedback, Dave, I have created the "real" work item here: http://exportsqlce.codeplex.com/workitem/17851
(The workitem relating to the Toolbox is simply to enable the option)

ErikEJ wrote Apr 16, 2012 at 6:57 PM

Can I ask you (Dave and Tom - and others) - does the option to ignore IDENTITY make any sense when scripting an entire database - I think only for individual tables? It will break referential integrity competely...

modao wrote Apr 16, 2012 at 8:10 PM

Hi Erik;

Without analyzing the CE Export Script; this of course depends on precisely WHERE you are setting integrity on the database. 
a) If it is the case that PK's, & IDENTITIES are created AFTER the INSERT of data into the database, then it is appropriate that the IDENTITY Column remains in the INSERT Statement.  

b) If on the other hand, it is the case that PK's, & IDENTITIES are created BEFORE the INSERT of data into the database, then it is appropriate that the IDENTITY Column be excluded from the INSERT Statement.
Providing it is the case that your existing SQL CE Export script, maintains the existing PK values for all tables when exporting the entire database, then yes, it would be acceptable to only implement this feature on per/table scripts. If your script does NOT maintain the existing PK values, then this would become extremely dangerous for any software that relies on any existing PK value (e.g. Root Level Look-Up tables).

Kind Regards Dave

wrote Jun 12, 2012 at 7:41 PM

Fixed on changeset 78620

Sign in to add a comment