Sql-server – Insert Into Multiple Tables At Once

insertsql server

I am migrating data between two databases. From the first database I am exporting a csv file that has the following columns:

Customer_Name
Contact_First_Name
Contact_Last_Name
Phone1
Phone2
Fax

I need to take this data and put it into the following tables for the new database:

Table: customer_contact
    contact_id  INT IDENTITY(1,1)
    first_name  VARCHAR(50)
    last_name   VARCHAR(50)
    customer_id INT
    PK = contact_id

Table: contact_phone
    contact_id INT
    phone_num  VARCHAR(15)
    in_use     BIT
    PK = contact_id, phone_num

Table: contact_fax
    contact_id INT
    fax_num    VARCHAR(15)
    in_use     BIT
    PK = contact_id, fax_num

My plan is to BULK INSERT the data from the csv into a temporary table, and then use that to do INSERTs into each of the other tables.

Since the main identifier for each contact if their first and last names, how do I insert the corresponding data into the phone and fax tables? I am using SQL Server.

Best Answer

This solution applies when you have repeated data in the CSV and you want to control what to do in every case. Since, there is nothing in your question about the following fields, I'm not including any logic for them: 

customer_id INT, 
in_use     BIT

0 - create the tables you already declared in your question

1 - create a t_Bulk table having the exact structure of your cvs and execute the bulk copy

CREATE TABLE T_BULK (
Customer_Name VARCHAR(100),
Contact_First_Name VARCHAR(50),
Contact_Last_Name VARCHAR(50),
Phone1 VARCHAR(15),
Phone2 VARCHAR(15),
Fax VARCHAR(15)
);
GO

2 - With the T_BULK table already full, let's process each record:

DECLARE @ContactId int, @FirstName varchar(50), @LastName varchar(50), @Phone1 varchar(50), @Phone2 varchar(50), @Fax varchar(50)

    DECLARE MY_CURSOR CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY  
    FOR   
    SELECT Contact_First_Name, Contact_Last_Name, Phone1, Phone2, Fax
    FROM dbo.T_BULK


    OPEN MY_CURSOR  
    FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax
    WHILE @@FETCH_STATUS = 0  
    BEGIN   

    //if the contact already exists we get the identity
    IF EXISTS (select contact_id from customer_contact where first_name=@FirstName and last_name=@LastName)
        select @ContactId = contact_id from customer_contact where first_name=@FirstName and last_name=@LastName

    //if the contact does not exit we need to insert and retrieve the new Identity value
    ELSE
        BEGIN
            insert customer_contact (first_name,last_name) values (@FirstName,@LastName)
            set @ContactId = SCOPE_IDENTITY()
        END

    //now we continue with the phones and fax
    //insert only new phones
    INSERT contact_phone (contact_id,phone_num) 
    SELECT Id, Phone   
    FROM (select @ContactId as Id, @Phone1 as Phone UNION select @ContactId ,@Phone2) P  
    WHERE NOT EXISTS (SELECT * FROM contact_phone C WHERE C.contact_id = P.Id and C.phone_num = P.Phone); 

    //insert only new fax
    INSERT contact_fax (contact_id,fax_num) 
    SELECT Id, fax   
    FROM (select @ContactId as Id, @Fax as fax) F
    WHERE NOT EXISTS (SELECT * FROM contact_fax C WHERE C.contact_id = F.Id and C.fax_num = F.fax); 


        FETCH NEXT FROM MY_CURSOR INTO @FirstName, @LastName, @Phone1, @Phone2, @Fax  
    END  

    CLOSE MY_CURSOR  
    DEALLOCATE MY_CURSOR

Good luck

In order to include an additional field:

in the declare zone include an aditional variable: @Extension.

which you must include in both FETCH NEXT lines. Then you need to include that variable when inserting the phones, but in this case you need to check that phone2 is not equeal to phone1, just to ensure you don't have repeated phones: 

    (select @contactId as Id, @Phone1 as Phone, @Extension as Extension 
UNION select @ContactId, @Phone2, null where @Phone2 <>@Phone1) P
Related Question