Avatar of pawnit
pawnit
 asked on

Approach to handling ID fields database importing

G'day Guys,

I'm just wanting to get some ideas on what approach people would take when faced with the same situation I am faced with at the moment.

We offer a software as a service solution to our clients; we use a MSSQL back end database. The majority of our clients previously used and old system written in Visual Fox Pro with a dbf database.

We would like to offer a facility which will import the data from the old VFP .dbf files into our MSSQL database.  I would have to write some form of script or something of the like as the data stored in these old .dbf's is all over the place and will need to be manipulated to fit into our database; Also what would be the best approach to handling the auto generated ID of these dbf tables. I cannot just import then as they will cause conflicts with data already in the MSSQL database as well as import of other customers databases.

Any tip or ideas would be greatly appreciated.
Microsoft SQL Server.NET ProgrammingFoxPro

Avatar of undefined
Last Comment
Olaf Doschke

8/22/2022 - Mon
kmslogic

When you are importing data include a client_id that is unique to each client you are importing.  The combination of that + their old ID will be unique, so you can use it for relationships in the old data, etc.
pawnit

ASKER
Hi Kmslogic,

I though of that however if i was to use a prefix starting with 1 and append it to an id of 1 the first id will become 11. if we look down to the 1001th record that becomes record 11001 this will conflict when you come to using 1100 as a prefix so when you add it to record 1 it also becomes 11001.

I know it might not be a change that we will get up to 1100 in prefixes but it proves that adding a prefix can still create duplicate id.
pawnit

ASKER
Or even simpler. client 2 on row 214 will create ID of 2214 as well as client 22 on row 14 will create ID 2214 as well.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
kmslogic

I'm talking about adding an additional field to the table, not doing some kind of string concatenation.
Cyril Joudieh

I did a small script once for a similar activity and I always prefer to use the standards of the latest database I am using.

So while importing the new records, I collect and the new ID's and map them to the old one. So as I am adding records from other tables, I use the new IDs.

So for example, I import clients, products, suppliers, ... Then I import purchase orders, invoices and so on so forth.
pawnit

ASKER
I'm not to sure that will work, they are both relational databases.  For example there is a customers table with a one to many relationship to the transactions table and then transactions table have a one to many relationship to the transaction items table.

If I take your approach I would have to add a new column to every table in the MSSQL database where there are relationships and then change all the SQL statements in the Application which would be a complete nightmare. Not to mention it will stuff up all the existing ID fields in the database.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cyril Joudieh

I am not sure how you created your database.

When I created my MIS database, I used character codes for the clients. If a client is called "Client X" then his code would look something like "C12345". This was based on a standard in FoxPro samples way back in late 80s.

So when I wrote the script for my client to move the data to MSSQL way back, I wrote it in such a way that "Client X" got a primary key of 1 (being first record added). In a map file which I used temporarily, I map "C12345" to 1. When the records are in the new database, C12345 does not exist anymore.

I am not sure if this applies to you. But this is how I did that one. Just an idea.
Cyril Joudieh

I am not sure how you created your database.

When I created my MIS database, I used character codes for the clients. If a client is called "Client X" then his code would look something like "C12345". This was based on a standard in FoxPro samples way back in late 80s.

So when I wrote the script for my client to move the data to MSSQL way back, I wrote it in such a way that "Client X" got a primary key of 1 (being first record added). In a map file which I used temporarily, I map "C12345" to 1. When the records are in the new database, C12345 does not exist anymore.

I am not sure if this applies to you. But this is how I did that one. Just an idea.
kmslogic

Well I'm assuming you are going to put the records for multiple clients in one table.  If that is the case you are going to have to change all the SQL statements in the application anyhow so that they don't see the other clients that are in the table.  Whatever way you do that (either by joining on both the client_id fields and the existing ID fields when relating the tables, or by filtering the table beforehand for a specific client ID and then just using the original SQL)

You really didn't describe what service you are really providing.  You said importing the data into your database from their old FoxPro systems, but that itself isn't really a service unless they are going to access or get the data back out of your system somehow.  I'd guess you are going to provide them with some kind of cloud-like access to their old data but it's not clear how their original old system will still be able to work with this new cloud-based version even if you only have one client and just leave everything pretty much identical on the SQL server side.

Could you give some more details and maybe I can give some concrete examples of what I'm talking about...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Olaf Doschke

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question