Insert dependent records in multiple tables, MSSERVER 2012

Question

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:

1. All Address Data (Street, State ...)
2. All Contact Data (Phone, Email ...)
3. All Company Data (Name, ...)

while linking each ID to the corresponding Foreign Key ID.

The three new tables have the format:

1. Company PK: ID, Name, Website, Rating, FK ContactData, FK Protocol, FK Documents
2. ContactData PK: ID, Phonenumber, Mobilenumber,Email, FK Address
3. 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

Answer 1

Given your description of what you are trying to do, I would take the below approach. I've put together a rather simplistic example for you, but I'm sure that you can modify it to suit your needs.

It assumes a single base table and then puts that data into three separate tables. The last table does a lookup into the Company and Address tables to get the ID's for the foreign keys.

/** Setup our Relational destination.  Normally would have Foreign Keys*/
DECLARE @AddressTable TABLE
    (
        AddressID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
        , AddressLine1 VARCHAR(100) NULL
        , City VARCHAR(100) NULL
        , [State] CHAR(2) NULL
        , ZipCode VARCHAR(10) NULL
    );

DECLARE @CompanyTable TABLE
    (
        CompanyID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
        , CompanyName VARCHAR(100) NOT NULL
    );

DECLARE @ContactTable TABLE
    (
        ContactID INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
        , PersonName VARCHAR(100) NULL
        , Email VARCHAR(50) NULL
        , PhoneNumber VARCHAR(10) NULL
        , CompanyID INT NULL
        , AddressID INT NULL
    );

/** Setup Our Source Data*/
DECLARE @SourceTable TABLE
    (
        CompanyName VARCHAR(100) NULL
        , PersonName VARCHAR(100) NULL
        , Email VARCHAR(50) NULL
        , PhoneNumber VARCHAR(10) NULL
        , AddressLine1 VARCHAR(100) NULL
        , City VARCHAR(100) NULL
        , [State] CHAR(2) NULL
        , ZipCode VARCHAR(10) NULL
    );

INSERT INTO @SourceTable
    (
        CompanyName
        , PersonName
        , Email
        , PhoneNumber
        , AddressLine1
        , City
        , [State]
        , ZipCode
    )
VALUES (
    'FunkyTown'
    , 'Jon Smith'
    , '[email protected]'
    , '1234567890'
    , '123 Funky'
    , 'Town'
    , 'VA'
    , '63487'
)
       , (
    'Microsoft'
    , 'Doe Really'
    , '[email protected]'
    , '0987654321'
    , '123 Red'
    , 'MS-Ville'
    , 'CA'
    , '90210'
)
       , (
    'FunkyTown'
    , 'Jane Awesome'
    , '[email protected]'
    , '1234567890'
    , '123 Funky'
    , 'Town'
    , 'VA'
    , '63487'
);

/** Perform Our Inserts 
    - Start with the tables without any Foreign Key Constraints
    - Make sure to only add distinct data.
    - Note also the self joins onto the destination table to prevent duplicates
    */
INSERT INTO @CompanyTable (CompanyName)
SELECT DISTINCT
    CompanyName
FROM @SourceTable
WHERE NOT (CompanyName IN (SELECT CompanyName FROM @CompanyTable));

INSERT INTO @AddressTable (AddressLine1, City, [State], ZipCode)
SELECT DISTINCT
    D.AddressLine1
    , D.City
    , D.[State]
    , D.ZipCode
FROM @SourceTable AS D
    LEFT OUTER JOIN @AddressTable AS A ON A.AddressLine1 = D.AddressLine1
                                           AND A.City = D.City
                                           AND A.[State] = D.[State]
                                           AND A.ZipCode = D.ZipCode
WHERE A.AddressID IS NULL;

INSERT INTO @ContactTable (PersonName, Email, PhoneNumber, CompanyID, AddressID)
SELECT D.PersonName
       , D.Email
       , D.PhoneNumber
       , C.CompanyID
       , A.AddressID
FROM @SourceTable AS D
    LEFT OUTER JOIN @CompanyTable AS C ON C.CompanyName = D.CompanyName
    LEFT OUTER JOIN @AddressTable AS A ON A.AddressLine1 = D.AddressLine1
                                           AND A.City = D.City
                                           AND A.[State] = D.[State]
                                           AND A.ZipCode = D.ZipCode
    LEFT OUTER JOIN @ContactTable AS D2 ON D2.PersonName = D.PersonName
                                            AND D2.Email = D.Email
                                            AND D2.PhoneNumber = D.PhoneNumber
                                            AND D2.CompanyID = C.CompanyID
                                            AND D2.AddressID = A.AddressID
WHERE D2.ContactID IS NULL;

/** Display Results */
SELECT CompanyID
       , CompanyName
FROM @CompanyTable;

SELECT AddressID
       , AddressLine1
       , City
       , [State]
       , ZipCode
FROM @AddressTable;

SELECT ContactID
       , PersonName
       , Email
       , PhoneNumber
       , CompanyID
       , AddressID
FROM @ContactTable;

Answer 2

But it will only inserts the last inserted ID from the Query before.

This is current as documented in https://learn.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://learn.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:

1. 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())
2. 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://learn.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.