March 28, 2011 at 11:47 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 12:14 am
why not import it into TempDB, then use a series of INSERTS to move the data where you want it? Or better, use Integration Services if you have to clean the data up before importing? Seems silly to drop a bunch of temporary records into your main database and then have to do maintenance on all that.
March 29, 2011 at 12:22 am
I am not sure about what you are proposing. The insert works in the first table but how can I insert into the second while maintaining the relationship. I need the foreign key to match the primary key of the first table. If the first one has a autonumeric field how can I insert into the second table and match that key with the new insert? At the moment I can't use integration services because I am using SQL Server 2008 R2 Express.
March 29, 2011 at 12:33 am
Sorry, couldn't see your database... (your head was in the way). I think the only way you can get the value of the Identity column for the update is to join on some other field that is unique in your record. Say for the sake of argument you have a bunch of records that look like this:
CREATE TABLE bad_data(
CompanyName varchar(50) not null,
City varchar(25) not null,
ZipCode char(10) not null
);
How do you know what makes a unique record? What (minimum) combination of columns guarantees a unique record? Once you have that, you can join on those fields to update your foreign keys.
March 29, 2011 at 1:03 am
Ok I found what makes a unique record, but now how I am going to be able to update the foreign keys so that they match with the other table? Could you give me some guidance please?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply