Solved Populate a new table with historic data, including primary key that auto-increments. (1 Viewer)

[xBirdman]

I have an historic table that should have been two (at least) because it mixes clients and cases. We have pulled all the clients, cleaned them as much as historic data allow, and deduplicated them. They are currently sitting in a spreadsheet. There is a second dataset that contains the links from this new table to the old clientinfo table. I'm not including big code blocks yet because I think it's pretty straightforward, but I'll add those if needed. I think it likely someone will be able to say, "step 1: do this, step 2: do that" and set me straight where I'm off the rail. Cheers to all.

I created a new client table and imported the client data into it, but could not get the Primary Key to auto-increment. I need to be able to supply the numbers initially, then have them increment from there. I found suggestions to import the data into the new table, then create a new field (personID) as an INT IDENTITY(1,1) field, copy the data over after setting the IDENTITY_INSERT to ON for the table. They then said to turn that setting off, and delete the original importID field. At that point when I add a new record via a form the personID field should auto-increment and away we go.

Setting up the table with the original columns (7) and importing the data work fine. When I send

SET IDENTITY_INSERT tblPersonClient ON;

I get a confirmation that it was successful.

Then when I run

UPDATE tblPersonClient SET personID = importID ;

I simply get an error

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'personID'.

So my table structure seems fine. I get no errors setting things up, but when I try to populate my new Identity field it won’t go. I have tried a couple of approaches to create the table and then load the data into it, and when I’ve gotten it there and try to add a record via the form, I get the, “you can’t add the record because the personID can’t be null” type error.

I’d be happy to hear your preferred steps for this process if you think I’ve got something wrong. My steps so far:

• Create new empty person table
• Add new integer field to clientinfo table (will hold the ID from the person table as the link)
• Load data via Append table to the new table
• At this point I want the ID field to auto-increment when new person added.
• Load link data (created outside of the database) to the clientinfo field.
• Set forms to use new person table as source on person searches and add new persons as needed.

 

[Minty]

My take on this would be to do the following, assuming you have the ID field already in the cleaned data (I think you have from your description?)

Import the data into your table with the ID field set as the primary key but NOT an identity field.
Once imported Then set the column as an Identity in the table design.

SQL Server will let you do this, access won't.

 

[Pat Hartman]

You can never update an existing row with a new value for the autonumber. You can ONLY append rows with existing autonumbers.

Create an empty table with the identity column defined.
Set the Identity Insert on
Run an append query. Include the original autonumber and append it to the identity field.

 

[Isaac]

Hence the verbiage "insert"

 

[xBirdman]

Thanks for the responses fellows! I'll give these a run-through once I'm over the e-mail hurdle from being out for a week.

I believe I tried both the suggested options above, but neither would work. It is highly probably I didn't ask SQL nicely enough... or precisely enough. Once I get some time on this today I'll either set this as solved or come back with specific questions. Thanks again, and here's to a hopeful 2021!

 

[Isaac]

I think the key is you have to do it all on SQL server - not Access.

1. turn identity insert on
2. run an Insert....on sql server, not access, if you must use Access make it a pass through query
3. turn identity insert off

 

[xBirdman]

You can never update an existing row with a new value for the autonumber. You can ONLY append rows with existing autonumbers.

Create an empty table with the identity column defined.
Set the Identity Insert on
Run an append query. Include the original autonumber and append it to the identity field.

This worked this time, so I must have set something up the first time I ran it and thought I'd done it this way.

I do have a follow-up question, for information only. This new table is the one side of a one:many relationship with another table, clientInfo. I had to add the foreign key field on the clientInfo table so they can be connected correctly. During my data clean I carried the unique ID for clientInfo foward, created the new personID for the new table, and then used the clientInfo.ID to fill the 'many' values for persons back to that table just using an update query. clientInfo has approximately 78K records and when I ran the update, I ended up with about 200 clientInfo records that remained null, even though they matched on the clientInfo.ID field. When I constrained the update query to only those 200 records that were left null and reran it, all the records filled in appropriately. Is there an upper limit on the number of records you can update that way? And if so, 78K seems pretty trifling given the size of some datasets.

Anyway, thanks for your help over this one bump. I really do value and love the helpful nature of this community. Have a good new year.

 

[Pat Hartman]

There is no upper limit. There must have been something wrong with your original update.

I thought you were trying to preserve the original PK's. If you had, you wouldn't have needed to update the FK. Just make sure to check carefully. Run queries with joins against both the old and the new and make sure they match.

 

[xBirdman]

Thanks Pat. The original PK's (multifield matching) were a mess, and created all kinds of errors when people entered erroneous case or sub-case numbers. I'm trying to 'fix' all of that as much as possible for the remainder of the anticipated lifespan of this database which is already a few years beyond it's planned usage.