Query to set column values

[craving94509]

Hello all,

New to the forum and hope someone can help me with an issue that has been driving me crazy.

Our company has acquired many different gas processing facilities that all run the same program (Maximo) in stand alone environments on Oracle. Each of these plants has exported there data to Access for me to load into a single Maximo SQL Server instance.

Each Access table has a primary key named "tablenameID", for this example we will use WORKORDERID. What this means is I have 7 different database exports each with a work order table, each with there own primary key sequence. Since I am tasked with moving these into a single database I need to renumber each export based on the max(workorderid) that is currently in the single instance of SQL Server. These primary key fields are text fields in Access.

What I am looking for is a query I can run that will prompt me for the starting number and sequentially number each record in the table.

Something similar to this:
Update workorder set workorderid = (prompt for number)

Then if I enter 150,000 each record should be sequentially numbered 150000, 150001, 150002

I have tried many different ways to work around this, but since many of these tables have 500K records, I thought a query would really make short work of this pestering issue.

If anyone has an idea on how to build this query I would surely appreciate it.

 

[craving94509]

Some additional information:

There is preexisting data in the SQL Server database with existing records.

SQL Server
select max(workorderid) from workorder
returns
100000

Access DB #1
I need to sequentially update the workorderid starting at 100001 (for 230,321 records)

Load data into SQL Server

SQL Server
select max(workorderid) from workorder
returns
330,321

Access DB #2
I need to sequentially update the workorderid starting at 330,322 (for 300,000 records)

Load data into SQL Server

SQL Server
select max(workorderid) from workorder
returns
630,321

And so on, and so on.....

Appreciate the help!

 

[dhookom]

I would push all the data to SQL Server for processing. Then post your question in Forum183. There is much greater functionality available with SQL Server.

Duane
Hook'D on Access
MS Access MVP

 

[craving94509]

Thanks dhookom,

I would love to do that but there is an index on the table that does not allow duplicate workorderid's. This field is also required on insert. Since I have multiple Access databases there will be duplicate workorderid's, and that is what I am trying to fix.

Maybe if I put it another way?

I have the following table "PEOPLE"
Name Title Series
DAVE BOSS NULL
AL BOSS NULL
JUNE BOSS NULL
JILL BOSS NULL
OMAR BOSS NULL
KARL BOSS NULL
PAUL BOSS NULL
MIKE BOSS NULL
WILL BOSS NULL

plus 200K more

My boss wants me to update each of the people records with a series number, and he wants the first number to start with 10,000

It seems like a simple request, but finding the solution is proving to be a challenge?

 

[dhookom]

You don't understand. My suggestion is to push the Access tables into temporary tables on SQL Server. You can then use functions like Row_Number().

Assume you have PEOPLE table which is permanent and tempPEOPLE which is temporary. You can use a statement like the following to begin numbering tempPeople records with one more than in People:

SELECT (SELECT Count(*) FROM PEOPLE) + ROW_NUMBER ( ) 
    OVER ( ORDER BY Lastname, FirstName) as RowNum, *
FROM TempPEOPLE

I believe any solution involves identifying a unique field(s) in the TempPeople to create the order of the numbering.

Duane
Hook'D on Access
MS Access MVP