I am trying to use bulk insert to insert data from a csv file into a sql server table, but it is returning 0 rows.
This is the bulk insert statement I am using
BULK INSERT dbo.DEA_Availability from 'C:\SQLPOWERDOC\CSV\DEA_Availability.csv'
with (
FIRSTROW=2,
FIELDTERMINATOR=',',
ROWTERMINATOR='\r\n',
ERRORFILE = 'C:\SQLPOWERDOC\CSV\DEA_Availability_ERROR.log',
DATAFILETYPE='char',
keepnulls
)
The table definition is:
CREATE TABLE [dbo].[DEA_AVAILABILITY](
[Server_Name] [varchar](max) NULL,
[Database_Name] [varchar](max) NULL,
[Priority] [varchar](max) NULL,
[Description] [varchar](max) NULL,
[Details] [varchar](max) NULL,
[URL] [varchar](max) NULL,
[IMPORT_DATE] [datetime2](7) NULL
) ON [PRIMARY]
GO
This is the contents of the CSV file
Server Name,Database Name,Priority,Description,Details,URL
HOSTNAME,,None,Cluster Node,This is a node in a cluster.,http://msdn.microsoft.com/en-us/library/ms189134(v=sql.100).aspx
I have verified that each line of the CSV file does end with \r\n
C:\SQLPOWERDOC\CSV\DEA_Availability_ERROR.log
is empty?ROWTERMINATOR='\r\n'
toROWTERMINATOR='\n'
orROWTERMINATOR = '0x0a'
. You have an extra column in table definition and you did not declare any default forimport_date
. I am not sureKEEPNULLS
will handle this column.