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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy