BULK INSERT and the ERRORFILE parameter

Can someone tell me if the ERRORFILE parameter is a valid (and
functioning) parameter for the BULK INSERT command when using SQL
Server 2000?
Finding documentation on this has been difficult, but it appears to be
a new parameter under the BULK INSERT command for 2005. If I look at
the list of parameters for this command under 2000, it doesn't appear
(see
http://msdn.microsoft.com/library/?...lref/ts_ba-b...
)
However if I run a BULK INSERT command under 2000 with the ERRORFILE
parameter, it doesn't give me a syntax error, it just appears to ignore
it.
Was the ERRORFILE option available under SQL 2000?
Thanks in advance,
Nick.Hi
ERRORFILE does not seem to produce and error in SQL 2000, but as you have
found it does seem to be ignored. The syntax in books online does not have
this option, therefore I think it is a new feature for SQL 2005. You could
use BCP if you really need this.
John
"Nick" wrote:

> Can someone tell me if the ERRORFILE parameter is a valid (and
> functioning) parameter for the BULK INSERT command when using SQL
> Server 2000?
> Finding documentation on this has been difficult, but it appears to be
> a new parameter under the BULK INSERT command for 2005. If I look at
> the list of parameters for this command under 2000, it doesn't appear
> (see
> http://msdn.microsoft.com/library/?...lref/ts_ba-b...
> )
> However if I run a BULK INSERT command under 2000 with the ERRORFILE
> parameter, it doesn't give me a syntax error, it just appears to ignore
> it.
> Was the ERRORFILE option available under SQL 2000?
> Thanks in advance,
> Nick.
>

BULK INSERT and the ERRORFILE parameter

Can someone tell me if the ERRORFILE parameter is a valid (and
functioning) parameter for the BULK INSERT command when using SQL
Server 2000?
Finding documentation on this has been difficult, but it appears to be
a new parameter under the BULK INSERT command for 2005. If I look at
the list of parameters for this command under 2000, it doesn't appear
(see
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_ba-b...
)
However if I run a BULK INSERT command under 2000 with the ERRORFILE
parameter, it doesn't give me a syntax error, it just appears to ignore
it.
Was the ERRORFILE option available under SQL 2000?
Thanks in advance,
Nick.Hi
ERRORFILE does not seem to produce and error in SQL 2000, but as you have
found it does seem to be ignored. The syntax in books online does not have
this option, therefore I think it is a new feature for SQL 2005. You could
use BCP if you really need this.
John
"Nick" wrote:
> Can someone tell me if the ERRORFILE parameter is a valid (and
> functioning) parameter for the BULK INSERT command when using SQL
> Server 2000?
> Finding documentation on this has been difficult, but it appears to be
> a new parameter under the BULK INSERT command for 2005. If I look at
> the list of parameters for this command under 2000, it doesn't appear
> (see
> http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_ba-b...
> )
> However if I run a BULK INSERT command under 2000 with the ERRORFILE
> parameter, it doesn't give me a syntax error, it just appears to ignore
> it.
> Was the ERRORFILE option available under SQL 2000?
> Thanks in advance,
> Nick.
>

BULK INSERT

Hi All,
I want to BULK INSERT from a text file to SQL but I only want to insert the
lines that begin with 'valid'. Is it possible? Is there a link where can i
find help with this problem?
Thanks!
joelConsider employing a staging table as the target of your BULK INSERT. You
can then use INSERT ...SELECT...WHERE to populate the main table with the
desired rows (e.g. WHERE MyColumn = 'valid').
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Gacosta" <joel@.gacosta.net> wrote in message
news:%23Jw6fTzPFHA.2584@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I want to BULK INSERT from a text file to SQL but I only want to insert
> the lines that begin with 'valid'. Is it possible? Is there a link where
> can i find help with this problem?
> Thanks!
> joel
>|||My source is a CSV text file and not from a database. How can i do this?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u0gqRm1PFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Consider employing a staging table as the target of your BULK INSERT. You
> can then use INSERT ...SELECT...WHERE to populate the main table with the
> desired rows (e.g. WHERE MyColumn = 'valid').
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joel Gacosta" <joel@.gacosta.net> wrote in message
> news:%23Jw6fTzPFHA.2584@.TK2MSFTNGP15.phx.gbl...
>|||> My source is a CSV text file and not from a database. How can i do this?
Let's say you have a csv with 3 fields containing the following records:
"valid",1,"A"
"invalid",2,"B"
"valid",3,"C"
Create a staging table with the columns you want to import:
CREATE TABLE StagingTable
(
Col1 varchar(10) NOT NULL,
Col2 int NOT NULL,
Col3 varchar(10) NOT NULL
)
Create a BCP format file that specifies the input file format and target
column mapping:
8.0
4
1 SQLCHAR 0 0 "\"" 0 unused ""
2 SQLCHAR 0 10 "\"," 1 Col1 ""
3 SQLCHAR 0 10 ",\"" 2 Col2 ""
4 SQLCHAR 0 10 "\"\r\n" 3 Col3 ""
Import the data into the staging table, specifying the table name, data file
and format file:
BULK INSERT dbo.StagingTable
FROM 'C:\Mydata.csv'
WITH
(
FORMATFILE='C:\Mydata.fmt'
)
Finally, insert the data into your main table:
INSERT INTO MainTable (Col2, Col3)
SELECT Col2, Col3
FROM dbo.StagingTable
WHERE Col1 = 'valid'
You might also consider using DTS so that you can filter the rows in an
ActiveX script and bypass the staging table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Gacosta" <joel@.gacosta.net> wrote in message
news:unZAfa7PFHA.3760@.TK2MSFTNGP10.phx.gbl...
> My source is a CSV text file and not from a database. How can i do this?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u0gqRm1PFHA.3716@.TK2MSFTNGP14.phx.gbl...
>|||Thanks Dan! I'll give it a try.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uiD7Sr8PFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Let's say you have a csv with 3 fields containing the following records:
> "valid",1,"A"
> "invalid",2,"B"
> "valid",3,"C"
> Create a staging table with the columns you want to import:
> CREATE TABLE StagingTable
> (
> Col1 varchar(10) NOT NULL,
> Col2 int NOT NULL,
> Col3 varchar(10) NOT NULL
> )
> Create a BCP format file that specifies the input file format and target
> column mapping:
> 8.0
> 4
> 1 SQLCHAR 0 0 "\"" 0 unused ""
> 2 SQLCHAR 0 10 "\"," 1 Col1 ""
> 3 SQLCHAR 0 10 ",\"" 2 Col2 ""
> 4 SQLCHAR 0 10 "\"\r\n" 3 Col3 ""
> Import the data into the staging table, specifying the table name, data
> file and format file:
> BULK INSERT dbo.StagingTable
> FROM 'C:\Mydata.csv'
> WITH
> (
> FORMATFILE='C:\Mydata.fmt'
> )
> Finally, insert the data into your main table:
> INSERT INTO MainTable (Col2, Col3)
> SELECT Col2, Col3
> FROM dbo.StagingTable
> WHERE Col1 = 'valid'
> You might also consider using DTS so that you can filter the rows in an
> ActiveX script and bypass the staging table.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joel Gacosta" <joel@.gacosta.net> wrote in message
> news:unZAfa7PFHA.3760@.TK2MSFTNGP10.phx.gbl...
>