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
I get a confirmation that it was successful.
Then when I run
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:
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.