title: How to create .fmt file, and export data into multiple tables from mixed-input file
body: <p>Am trying to set up a .fmt file using the bcp utility. I have been successfully able to create this before thus (using the table that data gets exported to as input):</p>
  
  <p>bcp cmsDatabase.dbo.Table1 format nul -T -c -f Table1Format.fmt</p>
  
  <pre><code>9.0
  3
  1       SQLCHAR       0       12      "\t"     1     Col1_Table1      ""
  2       SQLCHAR       0       100     "\t"     2     Col2_Table1      SQL_Latin1_General_CP1_CI_AS
  3       SQLCHAR       0       0       "\t"     3     Col3_Table1      SQL_Latin1_General_CP1_CI_AS
  </code></pre>
  
  <p>Table1 from which it was created:
  Col1_Table1 int (primary key)
  Col2_Table1 nvarchar(50)
  Col3_Table1 nvar</p>
  
  <p>Now I'm facing an issue. I have the input .txt/.csv file (delimited flat file) with data that will not be imported directly into one table, it has to go into many tables (AND, one directly goes into rows, other data needs to be input into columns of table2), Example Input File's data: </p>
  
  <pre><code>"Col1_Table1x" "Col2_Table1x" "Col3_Table1x" "Col1_Table2x, Col1_Table2y, Col1_Table2z"
  </code></pre>
  
  <p>Update:
  In the above file, values have to go into: </p>
  
  <pre><code>Col1_Tablex -&gt; Col1_Table1 column of Table1
  Col2_Tablex -&gt; Col2_Table1 column of Table1
  Col3_Table1x -&gt;  Col3_Table1 column of Table1
  </code></pre>
  
  <p>Table1:</p>
  
  <pre><code>Col1_Table1   Col2_Table1   Col3_Table1
  -------------------------------------------
  Col1_Table1x   Col2_Table1x   Col3_Table1x
  </code></pre>
  
  <p>Till here, I can get it to work. </p>
  
  <p><strong>What I'm trying to figure out:</strong></p>
  
  <p>"Col1_Table2, Col1_Table2, Col1_Table2, ....." -> Multiple records to be placed in Table2, populating Col_Table2 with the comma-seperated values, and 
  with Col1_Table1 as foreign key.</p>
  
  <p>i.e Table2 should have</p>
  
  <pre><code>    Col1_Table1      Col1_Table2
  ----------------------------------------------
      Col1_Table1x    Col1_Table2x
      Col1_Table1x    Col1_Table2y
      Col1_Table1x    Col1_Table2z
  </code></pre>
  
  <p>where Col1_Table2 needs to go as rows into Table2 (and is comma seperated), and Col1_Table1 is a foreign key for Table2 so it needs to be copied over too. </p>
  
  <p>Is there a way to create a .fmt file that will allow this kind of mixed-input to be copied over? </p>
  
  <p>Additional details: Each time I need to load up the tables from this input file, I can truncate all old data and re-populate. Any of the columns can have special characters like &lt;, " , &amp; etc. so is there a way to handle that too?</p>