"Carolyn" <postreply@dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
>
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
>
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
>
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
>
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
>
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>
>

"Jéjé" <willgart@BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@TK2MSFTNGP10.phx.gbl...
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
>
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
>
> "Carolyn" <postreply@dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@ursa-nb00s0.nbnet.nb.ca...
> >I am having a problem with bulk insert when the data in the input file
> > contains a decimal point. I have given a shortened example below, in
> > reality
> > the table has about 500 columns and about 5,000,000 records were
inserted
> > sucessfully before records with decimal points were encountered. I have
> > verified in the regional settings that the decimal point is the period.
> >
> > If necessary to solve the problem, I can easily drop the table, create
it
> > with different data types, and start the insert again. Getting the input
> > data changed would be considerably more difficult.
> >
> > CREATE TABLE [MY_TABLE] (
> > [ID] decimal (9,0) NOT NULL,
> > [CITY] varchar (50) NULL,
> > [BIRTH_DATE] datetime NULL,
> > [TOTAL_INCOME] decimal (10,2) NULL,
> > [NET_INCOME] decimal (10,0) NULL,
> > [NET_TAX_PAID] decimal (10,2) NULL)
> >
> > BULK INSERT MYDB.me.MY_TABLE
> > FROM 'd:\batch\data1999.txt'
> > WITH
> > (
> > FIELDTERMINATOR = '|',
> > ROWTERMINATOR = '|\n'
> > )
> >
> > 123456787|NEW YORK|1973/05/06|900|875|278|
> > 123456788|LONDON|1946/08/01|563.75|550|125.27|
> > 123456789|MADRID|1980/03/10|1067|987|338.27
> >
> > Server: Msg 4864, Level 16, State 1, Line 1
> > Bulk insert data conversion error (type mismatch) for row 2, column 4
> > (TOTAL_INCOME).
> > Server: Msg 4864, Level 16, State 1, Line 1
> > Bulk insert data conversion error (type mismatch) for row 3, column 6
> > (NET_TAX_PAID).
> >
> >
>
>