MSSQL - How to set default value in BULK INSERT?

I am trying to get data from a csv file with the following data.

Employee Name: Employee Id : Employee Type : Joining Date 
Henry   : 000123   : Permanent   : 01/01/2011  
Mathew  : 111524   : Contract    : 05/04/2011  
Steven  : 002544   : Permanent   : 07/12/2010  
Sophia  : 015474   :             : 29/02/2011
Rooney  : 111303   :             : 11/11/2010

Now I want to set

I have 2 cvs file and 2 format file where 1 for permanent type and 1 for contract type. Now I want to set the开发者_C百科 value in the table when no data provided. For an example, column 'Employee Type'. How do i set an default value where when i run the permanent employee cvs the 'Employee Type = Permanent' and when I run contract cvs 'Employee Type = Contract'.

BULK INSERT Employee_Table
    FROM 'C:\Employee.csv'  
    WITH (  
         FIELDTERMINATOR ='';'',  
         FIRSTROW = 2,
         ROWTERMINATOR = ''\n'' 
        )  

Use OPENROWSET so you can default the field:

INSERT [dbo].[Employee_Table]
  ([Employee Name], [Employee Id], [Employee Type], [Joining Date])
SELECT [Employee Name]
     , [Employee Id]
     , [Employee Type] = 'Permanent'
     , [Joining Date]
 FROM  OPENROWSET (BULK 'C:\Employee.csv',FORMATFILE='C:\formatfile.xml') as BulkLoadFile

http://msdn.microsoft.com/en-us/library/ms190312.aspx

ALternatively to What @Brian suggested, you can load to a staging table and then use t-sql to transform the data to the way you want it. You would do this if you need to do something more complex than a format file would allow. You also could do the transformations in an SSIS package rather than using Bulk insert.