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


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.

  • How are you planning to load the data? TSQL, Powershell or something else? Jul 12, 2018 at 18:27
  • Wow, can't believe I forgot that basic part >_< Editting OP Jul 12, 2018 at 18:32
  • @Goforebroke Simply put, I want to bulk insert into a temporary table with the csv data, and from there insert the data from the temp table into the tables on the new database Jul 12, 2018 at 18:34
  • Insert to customer_contact from the temp table, OUTPUT the contact_id values, insert into the two other tables. Wrap all 3 statements in a transaction.
    – Jacob H
    Jul 12, 2018 at 18:40
  • @JacobH This may work, I shall give it a shot. Thank you. Jul 12, 2018 at 18:44

3 Answers 3


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

Customer_Name VARCHAR(100),
Contact_First_Name VARCHAR(50),
Contact_Last_Name VARCHAR(50),
Phone1 VARCHAR(15),
Phone2 VARCHAR(15),

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)

    SELECT Contact_First_Name, Contact_Last_Name, Phone1, Phone2, Fax
    FROM dbo.T_BULK

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

    //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
            insert customer_contact (first_name,last_name) values (@FirstName,@LastName)
            set @ContactId = SCOPE_IDENTITY()

    //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  


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
  • Thank you very much for this response. Just a quick question. Let us suppose that there was another column to the CSV, that was an extension for the first phone number. How would I program that into the insert if the extension field could be blank, and didn't apply to the second number? (Assume I added a column to the phone table for the extension that allowed for nulls). Jul 16, 2018 at 12:50
  • 1
    check the answer....
    – AMG
    Jul 16, 2018 at 14:03

you have to use the KEEPIDENTITY when you use the BCP, this way the bulk insert will use the identity values you already have and not the autoincrement SQL Server provides.

Also, use staging tables before insert to the definitive tables, this to validate data consistency and format.


  • I'd love to use KEEPIDENTITY. However, problem is, there is no identity for the data rows I will be importing :| Jul 12, 2018 at 18:40
  • ohh it is easier then.. you just need a staging table with the raw data as it comes from the csv, just add an identity PK and let it be autogenerated by SQL Server engine, then insert the data on the first table customer_contact using your new ID as key, then create an aggregate query to insert info on the other 2 tables (you will also need a surrogate key for each table as well), if this is ok, i will update my answer :) Jul 12, 2018 at 19:03

This is how I would accomplish the task assuming there are no data issues with your CSV file. I assume first_name, last_name uniquely identifies a person eg 1 row per person...(usually another identifier is needed like middle name or middle init or bday or SSN or some combination to handle diff individuals with same name

1X Load

  1. Load the CSV file to a stage table that contains the needed columns via bulkinsert

BULK INSERT STG_Contacts FROM 'c:\contacts.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

  1. Insert into customer_contact table from the stage table

--not sure how you plan to gen customer_id so default it to null

insert into customer_contact(first_name, last_name, customer_id) select Contact_First_Name, Contact_LastName, NULL as Customer_ID from STG_Contacts

  1. insert into contact_phone table

-- default phone 1 to true phone 2 to false??

insert into contact_phone(contact_id, phone_num, in_use) select b.contact_id, a.Phone1, 'True' as InUse from STG_Contacts a join customer_contact b on a.Contact_First_Name = b.first_name and a.Contact_Last_Name = b.last_name where (Phone1 is not null and len(Phone1) > 0 ) union all select b.contact_id, a.Phone2, 'False' as InUse from STG_Contacts a join customer_contact b on a.Contact_First_Name = b.first_name and a.Contact_Last_Name = b.last_name where (Phone2 is not null and len(Phone2) > 0 )

  1. insert into fax table

insert into contact_fax(contact_id, fax_num, in_use) select b.contact_id, a.fax, 'True' as InUse from STG_Contacts a join customer_contact b on a.Contact_First_Name = b.first_name and a.Contact_Last_Name = b.last_name where (fax_num is not null and len(fax_num) > 0 )

Nightly loading...via SSIS or TSQL via SQL Agent

  1. Truncate the staging table
  2. Load the staging table from csv w/bulk insert
  3. Load the contact table with new rows and process updates
  4. Load the phone and fax table with new rows and process updates

use this link on how to use the merge statement to process new rows, updates and deletes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.