BULK INSERT into table with Foreign Key

Question

I have a table Customer which has the details of the customer. The following are the fields

CustId (PrimaryKey), Name, Date of Birth

I have another table, the Asset information. The following fields are there -

AssetId (PrimaryKey), AssetValue, CustId (Foreign Key Reference) 

My CSV file is as such

Name, Date of Birth, AssetValue 

and I have to insert it into the two tables. I split the CSV file, one with the Name of Date of Birth and the other with just the AssetValue.

Here is what I did -

/*Creation of Table*/
CREATE TABLE Customer
(
    custid int identity(1,1) not null, 
    name nvarchar(50) not null, 
    dateofbirth datetime not null, 
    primary key (custid) 
)
CREATE TABLE Asset
(
    AssetId int identity(1,1) not null, 
    AssetDollars money not null, 
    primary key (AssetId),
    CustId int foreign key references Customer(custid)
)

For the bulk insert what I did was this. I created a view for the Customer with the two fields Name and Date of Birth and then inserted the records.

Here is what I did -

CREATE view vw_bulk_insert_customer
AS
    SELECT name, dateofbirth FROM customer

BULK  INSERT vw_bulk_insert_customer
FROM 'C:\Customer.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

This worked perfectly fine.

Now, how do I insert it into the Asset table with the CustId (as it is not available in the CSV file).

I am not allowed to change the CSV file. I can split the CSV file, that is allowed.

I did not know how to do this...Any thoughts?

Answer 1

you can use disable novalidate over foreign key. once you have imported all your data you can enable novalidate over foreign key.The same can be done with primary key also. The benefit with novalidate option is your previous data wont be checked according to rules of that constraint but the datas after it are hence checked. if u want i could paste the entire syntax to do the same