I'm trying to migrate Data from a Excel file to a newly build Database.
I already inserted all data from the Excel file to the Database and now need to insert it in three newly dependable tables.
The old database contains all data in a single table, in the new Database I would like to split the Data in three different tables (Address, ContactData and Company).
I tried doing it like this:
INSERT INTO CRM_Beta.dbo.Adress(Street, City, ZIP, State, Country)
(SELECT Adress, City, ZIP, State, Country
FROM ACT_Data.dbo.ActData)
INSERT INTO CRM_Beta.dbo.Contactdata(Phonenumber, Mobilenumber, Email, Adress)
(SELECT PhoneNumber, MobileNumber, [Privat-Email], IDENT_CURRENT('Adress')
FROM ACT_Data.dbo.ActData)
INSERT INTO CRM_Beta.dbo.Company(Name, Website, Rating, Contactdata, Protocol, Documents)
(SELECT Company, [Web-Site], '', IDENT_CURRENT('Contactdata'), @@ROWCOUNT, @@ROWCOUNT
FROM ACT_Data.dbo.ActData WHERE Company != NULL)
But it will only inserts the last inserted ID from the Query before.
Lets say the first table contains two entries in the format:
| Address | City | ZIP | State | Country | PhoneNumber | MobileNumber | private_mail | Company
Now i need to split this into three tables:
- All Address Data (Street, State ...)
- All Contact Data (Phone, Email ...)
- All Company Data (Name, ...)
while linking each ID to the corresponding Foreign Key ID.
The three new tables have the format:
- Company PK: ID, Name, Website, Rating, FK ContactData, FK Protocol, FK Documents
- ContactData PK: ID, Phonenumber, Mobilenumber,Email, FK Address
- Address PK: ID, Street, City, ZIP, State, Country
I can change the Database if necassary or redesign if you think im doing something stupid ;)
Thanks for the help.
Edit1: I have tried using OUTPUT but i didn't figure out how to work with it yet and therefore have problems applying it to my case.
The Source file is a simple txt file i imported with the SQL-Server Assistant, so i could easily add keys or indexes, or use custom imports.
Edit2: I managed to do what i tried to accomplish in a similiar way that @Jonathan Fite showed, it definitely lead me into the right direction.
I did not understand how this would have worked with the Identity_Insert mentioned by @David Spillett and therefore will give the right answer to @Jonathan Fite