title: SQL Insert Out of Sync
body: <p>I have a bit of SQL here which is throwing an error:</p>
  
  <pre><code>DROP TABLE HACP_TEMP_PIC_HCV_Imported;
  
  CREATE TABLE HACP_TEMP_PIC_HCV_Imported
  (
      HeadSSN varchar(255) NOT NULL,
      HeadFName varchar(255) NOT NULL,
      HeadMName varchar(255),
      HeadLName varchar(255) NOT NULL,
      ModifiedDate varchar(255) NOT NULL,
      ActionType varchar(255) NOT NULL,
      EffectiveDate varchar(255) NOT NULL
  );
  
  BULK INSERT HACP_TEMP_PIC_HCV_Imported
  FROM 'C:\Work\MTWAdhocReport.csv'
  WITH
  (
      FIRSTROW = 11,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      ERRORFILE = 'C:\Work\Import_ErrorRows_HCV.csv',
      TABLOCK
  );
  
  UPDATE HACP_TEMP_PIC_HCV_Imported
  SET HeadSSN = REPLACE(HeadSSN, '"', ''),
      HeadFName = REPLACE(HeadFName, '"', ''),
      HeadMName = REPLACE(HeadMName, '"', ''),
      HeadLName = REPLACE(HeadLName, '"', ''),
      ModifiedDate = REPLACE(ModifiedDate, '"', ''),
      ActionType = REPLACE(ActionType, '"', ''),
      EffectiveDate = REPLACE(REPLACE(EffectiveDate, '"', ''),',','');
  
  DROP TABLE HACP_PIC_HCV_Imported;
  
  CREATE TABLE HACP_PIC_HCV_Imported
  (
      HeadSSN varchar(255) NOT NULL,
      HeadFName varchar(255) NOT NULL,
      HeadMName varchar(255),
      HeadLName varchar(255) NOT NULL,
      ModifiedDate varchar(255) NOT NULL,
      ActionType int NOT NULL,
      EffectiveDate varchar(255) NOT NULL
  );
  
  INSERT INTO HACP_PIC_HCV_Imported(HeadSSN, HeadFName, HeadMName, HeadLName, ModifiedDate, ActionType, EffectiveDate)
      SELECT
          LTRIM(HeadSSN),
          LTRIM(HeadFName),
          LTRIM(HeadMName),
          LTRIM(HeadLName),
          LTRIM(ModifiedDate),
          CONVERT(int, LTRIM(ActionType)),
          LTRIM(EffectiveDate)
      FROM 
          HACP_TEMP_PIC_HCV_Imported;
  </code></pre>
  
  <p>Stepping through this, creating the temp table and importing the CSV into it works fine. Updating the table to remove quotes and a trailing comma from the <code>EffectiveDate</code> column works. Creating the new table-proper works.</p>
  
  <p>When trying to copy the data into the second table (and converting <code>ActionType</code> into an <code>INT</code>), I get this error message:</p>
  
  <blockquote>
    <p>Conversion failed when converting the varchar value '4/07/2016' to data type int.</p>
  </blockquote>
  
  <p>That data is the second row value in <code>ModifiedDate</code>, so the columns are apparently getting out of sync after importing the first row. I have double-checked that all of the data is in the proper columns after being imported into the temp table initially.</p>
  
  <p>Any thoughts? I feel like I'm missing something obvious.</p>