March 28, 2011 at 9:30 pm
Hi guys:
I have an excel database with 20000 that I need to convert into SQL server.
I already imported all the records into sql server, but they are in one table.
What I need to do is separate them into different tables that are already created maintaining the relationships between the records. Because its an excel spreadsheet it is redundant.
EX:
Company Address Telephone
ABC 123 Ok St 123456
ABC 123 OK St 000000
ABC 44 Fake St 444444
In terms of database design I will have a company table with the name
An address table with the address
A telephone table with the telephones
Where Company 1-M Address
Address 1-M Telephone
(1-M) One to many relationship
So the result of this inside the database will be:
1 record in company
2 Records in address
3 Records in Telephone
How can I achieve this maintaining the relationships between the records?
March 29, 2011 at 2:47 pm
Step 1: verify the data make sense in terms of describing the business scenario.
Example: Do you have rows in your import table with "Company A", "Company A Inc.", "Company A, Inc." referring to the same company or "Ok St" and "Ok Street" for the same address?
Step 2: define the target table structure (including proper constraints) and verify the import data are qualified.
Example: Do you need separate columns to split the address and if so, is the address always available in the same format? If not, how to deal with it?
Step 3: Insert the distinct Company values in a table with an identity column.
Step 4: Join this table back to your import table on Company column and Insert the distinct CompanyID and splitted_Address columns into the address table.
Step 5: Join the compan table back to your import table on Company column and Insert the distinct CompanyID and Telephone column into the Telephone table.
Step 6: Find a solution for all import errors due to constraint violations.
Step 7: archive or delete the import table.
March 29, 2011 at 2:55 pm
Hi LutzM:
This makes sense. I am going to try it in the afternoon and post back the results. Thanks for that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply