Insert Into Multiple Tables At Once

Question

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.

• Goforebroke: How are you planning to load the data? TSQL, Powershell or something else?
• Skitzafreak: Wow, can't believe I forgot that basic part >_< Editting OP
• Skitzafreak: @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
• Jacob H: 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.
• Skitzafreak: @JacobH This may work, I shall give it a shot. Thank you.

Answer 1

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

• Skitzafreak: 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).
• AMG: check the answer....

Answer 2

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.

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/keep-identity-values-when-bulk-importing-data-sql-server?view=sql-server-2017

• Skitzafreak: I'd love to use KEEPIDENTITY. However, problem is, there is no identity for the data rows I will be importing :|
• Eduardo Pivaral: 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 :)

Answer 3

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' )

2. 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

3. 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 )

4. 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.