Insert dependent records in multiple tables, MSSERVER 2012 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
But it will only inserts the last inserted ID from the Query before.


This is current as documented in https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql:


  Returns the last identity value generated for a specified table or view.
  The last identity value generated can be for any session and any scope.


You can get all the identities generated in an insert using the OUTPUT clause as documented at https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql


  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.


Assuming that you don't need to deduplicate anything at this point (i.e. you won't have two companies with the same address), and you do not need to keep ID values unique between the tables, you could:


Add an ID column to the source table of the right type (INT, UNIQUEIDENTIFIER, ...) which is populated by default (i.e. IDENTITY(1,1) or DEFAULT NEWID())
Use that for the IDs of all the inserts coming from it (if you are using IDENTITY columns you will need to temporarily set IDENTITY_INSERT off for them as this is done, see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql


No need for OUTPUT or extra lookups.


  I can change the Database if necassary or redesign if you think im doing something stupid ;)


You'll need to more thoroughly describe what you are trying to model before we can help at all with that. It does seem strange to me that a company record would have a reference to contact details rather than the other way around: if is more likely that a company would have several contact methods than that one contact method could be relevant to more then one company.