June 3, 2009 at 8:04 am
Hi,
I need to import data from an Access mdb into existing SQL tables where the Identity Seed values in the SQL tables (numeric primary keys) retain the same value as the corressponding records in the Access tables.
Is this as simple as setting the 'Is Identity' property to 'No' prior to the import and then to 'Yes' on completion? These are incremental integers (Start at 1 and increment by 1), so I'm guessing that the Identity Increment and Identity Seed values will both be set to '1'.
Or is there anything I should be aware of?
I have already tried the above (Setting the 'Is Identity' property to 'No') via the Import wizard. But the procedure failed as Error 0xc020249 'Failure inserting into the read-only column 'adIndex'.
Note:
There may be instances where there are no values (Null) in the Access tables. Is this likely to cause an error?
Thanks in advance,
Neal
June 3, 2009 at 9:21 am
You can issue the command Set Identity_Insert [TableName] On before you start the insert and then Set Identity_Insert [TableName] Off when you are done.
Yes, nulls will cause a problem because Identity Columns do not allow NULLs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 3, 2009 at 9:26 am
It's Ok... I found out how to resolve this (I hope).
On the Import Wizard, there is a setting on the Column Mappings called Enable identity insert. Select this tick-box.
Microsoft definition:
Choose this option to allow existing identity values in the source data to be inserted into an identity column in the destination table. By default, the destination identity column does not allow this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply