Bulk Insert to EXT Table and retain Dex_Id's

[onpnt]

I have to bulk insert some data into a few EXT tables and would like to know how the dex_row_id is incremented and if this can be updated while in the insert process?

Task is pretty straight forward and I'm not sure there is much more to it then that but my GP knowledge is fresh so if you need anything more as far as details go please fire away

Cheers

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[onpnt]

I'm hoping I don't have to do what is outlines below to do an insert

Data Repair. You should be ready to save GP table prior to data repair: select * into SOP10100_Copy from SOP10100 and then use update statement to fix incorrect values. If you need to load SOP10100 back from the backup table – first drop DEX_ROW_ID column and then use: insert into SOP10100 select * from SOP10100_Copy.

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[wltenney]

dex_row_id is an identity column. The value is automatically supplied by SQL each time a row is inserted into the table. Therefore, you should not include this column when importing your data. If you are using an INSERT statement for the import, you will need to specify all of the table columns except dex_row_id.

Note that in the example you included in your second post, the instructions to drop the dex_row_id column refer to SOP10100_copy, not to SOP10100. The reason for this is to be able to use the wildcard (*) rather than having to specify all of the individual columns in the insert statement.

 

[onpnt]

>>dex_row_id is an identity column

I know this. My main question is what is it for and how will it affect the GP interface by forcing data into the tables outside the applications normal process

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[wltenney]

>>My main question is what is it for and how will it affect the GP interface by forcing data into the tables outside the applications normal process.

There should be no problem related to the dex_row_id column. I don't know of any situations where the application relies on data in this column. There are, of course, other table relationships which must be maintained, depending on what you are importing. GP relies primarily on the application to maintain relational integrity.

 

[winthropdc]

The Dex_Row_ID is an identity column added to all Dexterity tables when they are created.

The reason it is there is that Dexterity does not require a table to have a unique primary key. This was fine when working with Ctree, and P.SQL (BTrieve), but MS SQL does need a unique primary key.

So when MS SQL Support was implemented for Dexterity, the Dex_Row_ID column was added to table so the identity field could be used to make a unique SQL key. For example the GL20000 table.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer - Great Plains
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)

http://www.microsoft.com/Dynamics

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[onpnt]

Great explanations. Thank you

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]