OK, I tried this:
USE Alert_db;
BULK INSERT funds FROM 'C:\\data\\myDa ta.dat'
WITH (FIELDTERMINATO R='\t',
KEEPNULLS,
ROWTERMINATOR=' \r\n');
And I got the following errors.
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for
the specified codepage) for row 1, column 4 (f_asset_classe s_id).
Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row
1, column 6. Verify that the field terminator and row terminator are
specified correctly.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an
error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server
"(null)".
One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.
I do not know what "State 1" vs "State 8" is supposed to mean.
The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?
In other cases, where I have to use something like bulk insert,
involves several columns containing dates. I know MS SQL supports the
format used in the file (by reading the documentation for cast
operations), but is there an easy way to tell MS SQL which of the
supported date formats to use when reading this data. I've read bcp
should be useful for this, but I have yet to figure that out.
In about half of the cases where I load data from a file, the data is
loaded once when the database is first created, and in the rest, there
is new data to be loaded every business day; so I need to be able to
submit the required command from the command line, and thus invoke it
using a perl script.
BTW: I have ordered a couple books on T-SQL, but they have yet to
arrive.
Thanks
Ted
USE Alert_db;
BULK INSERT funds FROM 'C:\\data\\myDa ta.dat'
WITH (FIELDTERMINATO R='\t',
KEEPNULLS,
ROWTERMINATOR=' \r\n');
And I got the following errors.
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for
the specified codepage) for row 1, column 4 (f_asset_classe s_id).
Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row
1, column 6. Verify that the field terminator and row terminator are
specified correctly.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an
error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server
"(null)".
One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.
I do not know what "State 1" vs "State 8" is supposed to mean.
The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?
In other cases, where I have to use something like bulk insert,
involves several columns containing dates. I know MS SQL supports the
format used in the file (by reading the documentation for cast
operations), but is there an easy way to tell MS SQL which of the
supported date formats to use when reading this data. I've read bcp
should be useful for this, but I have yet to figure that out.
In about half of the cases where I load data from a file, the data is
loaded once when the database is first created, and in the rest, there
is new data to be loaded every business day; so I need to be able to
submit the required command from the command line, and thus invoke it
using a perl script.
BTW: I have ordered a couple books on T-SQL, but they have yet to
arrive.
Thanks
Ted
Comment