Sql-server – Insert dependent records in multiple tables, MSSERVER 2012

dependenciesforeign keyinsertsql-server-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:

  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

Best Answer

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'
    , 'jsmith@funkytown.com'
    , '1234567890'
    , '123 Funky'
    , 'Town'
    , 'VA'
    , '63487'
)
       , (
    'Microsoft'
    , 'Doe Really'
    , 'dreally@notmicrosoft.com'
    , '0987654321'
    , '123 Red'
    , 'MS-Ville'
    , 'CA'
    , '90210'
)
       , (
    'FunkyTown'
    , 'Jane Awesome'
    , 'jawesome@funkytown.com'
    , '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;
Related Question