title: SQL Server BULK INSERT FROM different schemas
body: <p>I have a database that can have data updated from two external parties.
  Each of those parties sends a pipe delimited text file that is BULK INSERTED into the staging table.
  I now want to change the scheme for one of the parties by adding a few columns, but this is unfortunately breaking the BULK INSERT for the other party even though the new columns are all added as NULLABLE.</p>
  
  <p>Is there any obvious solution to this?</p>
  
  <p>TABLE SCHEMA:</p>
  
  <pre><code>CREATE TABLE [dbo].[CUSTOMER_ENTRY_LOAD](
  [CARD_NUMBER] [varchar](12) NULL,
  [TITLE] [varchar](6) NULL,
  [LAST_NAME] [varchar](34) NULL,
  [FIRST_NAME] [varchar](40) NULL,
  [MIDDLE_NAME] [varchar](40) NULL,
  [NAME_ON_CARD] [varchar](26) NULL,
  [H_ADDRESS_PREFIX] [varchar](50) NULL,
  [H_FLAT_NUMBER] [varchar](5) NULL,
  [H_STREET_NUMBER] [varchar](10) NULL,
  [H_STREET_NUMBER_SUFFIX] [varchar](5) NULL,
  [H_STREET] [varchar](50) NULL,
  [H_SUBURB] [varchar](50) NULL,
  [H_CITY] [varchar](50) NULL,
  [H_POSTCODE] [varchar](4) NULL,
  [P_ADDRESS_PREFIX] [varchar](50) NULL,
  [P_FLAT_NUMBER] [varchar](5) NULL,
  [P_STREET_NUMBER] [varchar](10) NULL,
  [P_STREET_NUMBER_SUFFIX] [varchar](5) NULL,
  [P_STREET] [varchar](50) NULL,
  [P_SUBURB] [varchar](50) NULL,
  [P_CITY] [varchar](50) NULL,
  [P_POSTCODE] [varchar](4) NULL,
  [H_STD] [varchar](3) NULL,
  [H_PHONE] [varchar](7) NULL,
  [C_STD] [varchar](3) NULL,
  [C_PHONE] [varchar](10) NULL,
  [W_STD] [varchar](3) NULL,
  [W_PHONE] [varchar](7) NULL,
  [W_EXTN] [varchar](5) NULL,
  [DOB] [smalldatetime] NULL,
  [EMAIL] [varchar](50) NULL,
  [DNS_STATUS] [bit] NULL,
  [DNS_EMAIL] [bit] NULL,
  [CREDITCARD] [char](1) NULL,
  [PRIMVISACUSTID] [int] NULL,
  [PREFERREDNAME] [varchar](100) NULL,
  [STAFF_NUMBER] [varchar](50) NULL,
  [CUSTOMER_ID] [int] NULL,
  [IS_ADDRESS_VALIDATED] [varchar](50) NULL
  ) ON [PRIMARY]
  </code></pre>
  
  <p>BULK INSERT STATEMENT:</p>
  
  <pre><code>SET @string_temp = 'BULK INSERT customer_entry_load FROM '+char(39)+@inpath
  +@current_file+'.txt'+char(39)+' WITH (FIELDTERMINATOR = '+char(39)+'|'+char(39)
  +', MAXERRORS=1000,  ROWTERMINATOR = '+char(39)+'\n'+char(39)+')'
  SET DATEFORMAT dmy
  EXEC(@string_temp)
  </code></pre>