title: BULK INSERT into table with Foreign Key
body: <p>I have a table Customer which has the details of the customer. The following are the fields</p>
  
  <pre><code>CustId (PrimaryKey), Name, Date of Birth
  </code></pre>
  
  <p>I have another table, the Asset information. The following fields are there - </p>
  
  <pre><code>AssetId (PrimaryKey), AssetValue, CustId (Foreign Key Reference) 
  </code></pre>
  
  <p>My CSV file is as such</p>
  
  <pre><code>Name, Date of Birth, AssetValue 
  </code></pre>
  
  <p>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. </p>
  
  <p>Here is what I did - </p>
  
  <pre><code>/*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)
  )
  </code></pre>
  
  <p>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. </p>
  
  <p>Here is what I did - </p>
  
  <pre><code>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'
  )
  </code></pre>
  
  <p>This worked perfectly fine. </p>
  
  <p>Now, how do I insert it into the Asset table with the CustId (as it is not available in the CSV file). </p>
  
  <p>I am not allowed to change the CSV file. I can split the CSV file, that is allowed. </p>
  
  <p>I did not know how to do this...Any thoughts?</p>