Bulk Insert only inserting every 2nd row

Bulk Insert only inserting every 2nd row

Post by frisky weas » Mon, 08 Jul 2002 12:52:23



Hi -
I don't have a whole lot of experience with using BULK INSERT, and am
running into a problem that should have a very simple solution (i
think), but I'm apparently just not getting it. Here's what i have:

Bulk Insert Statement:

BULK INSERT [addresses]
From 'C:\MYTEXTFILE.txt'
WITH
(
FIELDTERMINATOR = '\n',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)

Sample data from MYTEXTFILE.txt:
Username1
Username2
Username3
Username4
Username5
Username6
Username7
Username8
Username9
Username10

wHAT IS BEING RETURNED NOW:
Username2
Username4
Username6
Username8
Username10

I was thinking it had something to do with specifiying a
"FIELDTERMINATOR" when I really only have ONE field - My first thought
was to completely remove FIELDTERMINATOR, and just use
ROWTERMINATOR...but this threw errors - The command DOES run
successfully, but each time it Inserts exactly HALF the number of
records that are in my source text file.

 
 
 

Bulk Insert only inserting every 2nd row

Post by oj » Mon, 08 Jul 2002 16:12:35


A quick test shows it works fine...Would you elaborate on the error...



select top 10 ''username''+cast(orderid-10247 as varchar) from
northwind..orders order by orderid" '


go

create table addresses(x varchar(255))
go
BULK INSERT [addresses]
From 'C:\MYTEXTFILE.txt'
WITH
(
ROWTERMINATOR = '\n',
MAXERRORS = 0
)

go
select * from addresses
go
drop table addresses
exec master..xp_cmdshell 'del c:\mytextfile.txt',no_output
go

--
-oj
Rac v2.1 coming soon
http://www.rac4sql.net


Quote:> Hi -
> I don't have a whole lot of experience with using BULK INSERT, and am
> running into a problem that should have a very simple solution (i
> think), but I'm apparently just not getting it. Here's what i have:

> Bulk Insert Statement:

> BULK INSERT [addresses]
> From 'C:\MYTEXTFILE.txt'
> WITH
> (
> FIELDTERMINATOR = '\n',
> ROWTERMINATOR = '\n',
> MAXERRORS = 0
> )

> Sample data from MYTEXTFILE.txt:
> Username1
> Username2
> Username3
> Username4
> Username5
> Username6
> Username7
> Username8
> Username9
> Username10

> wHAT IS BEING RETURNED NOW:
> Username2
> Username4
> Username6
> Username8
> Username10

> I was thinking it had something to do with specifiying a
> "FIELDTERMINATOR" when I really only have ONE field - My first thought
> was to completely remove FIELDTERMINATOR, and just use
> ROWTERMINATOR...but this threw errors - The command DOES run
> successfully, but each time it Inserts exactly HALF the number of
> records that are in my source text file.


 
 
 

Bulk Insert only inserting every 2nd row

Post by frisky weas » Tue, 09 Jul 2002 04:21:46


Well I'm a bit ashamed to admit to the rookie move I made, but
everyone tried to help me, so I figure I owe it to you guys : )

This is what I did wrong:

I had a text file with only ONE column - So when I created my target
table, did I also make sure it had only ONE column to insert into? NO
I didn't.

I did, without even thinking, what I've done a thousand times before
when creating a new table - I created my target destination field, but
I ALSO created an ID field, as a primary key, auto increment.

Well I guess when I ran my BULK insert statement, it got really
confused that I was taking a text file with only 1 column and trying
to place it into a table with TWO columns. : )

As I said, I'm a real rookie with the BULK insert statement, but after
hearing you guys' replies, I felt pretty sure that my statement was
right. I decided to look into other areas, and that's when I realized
my mistake about my table layout - too many darn columns! So your
input did really help me a lot - otherwise I might still be looking
for ways to change my BULK insert statement -

Thanks to everybody for their input - I'm also going to post this
message into the other 1 or 2 groups that i posted to:

thanks again to everyone!

 
 
 

Bulk Insert only inserting every 2nd row

Post by Steve Kas » Mon, 08 Jul 2002 20:25:28


Your code worked for me in SQL Server 2000, service pack 2 and
inserted every row.  It also worked if FIELDTERMINATOR was
deleted, or in fact, set to anything - I think FIELDTERMINATOR
is ignored if there is only one column.

My guess is that the problem you are having is the result of something
you haven't mentioned.  The most obvious thing that would cause this
is an identity column in the table.  Since you didn't provide a CREATE
TABLE statement, I don't know if you have an identity column.

It's important to give all the details.  Can you provide the CREATE TABLE,
and tell what version/service pack you're running?

If you have an identity problem, a solution (that I first saw posted by Umachandar
Jayachandran) is

create view AddressesNoId
as select address from Addresses

BULK INSERT AddressesNoId ...

Steve Kass
Drew University

WITH
(
ROWTERMINATOR = '\n',
MAXERRORS = 0
)

WITH
(
FIELDTERMINATOR = '\0',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)


> Hi -
> I don't have a whole lot of experience with using BULK INSERT, and am
> running into a problem that should have a very simple solution (i
> think), but I'm apparently just not getting it. Here's what i have:

> Bulk Insert Statement:

> BULK INSERT [addresses]
> From 'C:\MYTEXTFILE.txt'
> WITH
> (
> FIELDTERMINATOR = '\n',
> ROWTERMINATOR = '\n',
> MAXERRORS = 0
> )

> Sample data from MYTEXTFILE.txt:
> Username1
> Username2
> Username3
> Username4
> Username5
> Username6
> Username7
> Username8
> Username9
> Username10

> wHAT IS BEING RETURNED NOW:
> Username2
> Username4
> Username6
> Username8
> Username10

> I was thinking it had something to do with specifiying a
> "FIELDTERMINATOR" when I really only have ONE field - My first thought
> was to completely remove FIELDTERMINATOR, and just use
> ROWTERMINATOR...but this threw errors - The command DOES run
> successfully, but each time it Inserts exactly HALF the number of
> records that are in my source text file.

 
 
 

Bulk Insert only inserting every 2nd row

Post by frisky weas » Tue, 09 Jul 2002 04:37:23


Steve -

I believe you described EXACTLY what th problem was - I'm posting this
new updated message (see below)  - to every place I originally posted
my problem. I did indeed find the problem, and I believe it's EXACTLY
what you described:

" Well I'm a bit ashamed to admit to the rookie move I made, but
everyone tried to help me, so I figure I owe it to you guys : )

This is what I did wrong:

I had a text file with only ONE column - So when I created my target
table, did I also make sure it had only ONE column to insert into? NO
I didn't.

I did, without even thinking, what I've done a thousand times before
when creating a new table - I created my target destination field, but
I ALSO created an ID field, as a primary key, auto increment.

Well I guess when I ran my BULK insert statement, it got really
confused that I was taking a text file with only 1 column and trying
to place it into a table with TWO columns. : )

As I said, I'm a real rookie with the BULK insert statement, but after
hearing you guys' replies, I felt pretty sure that my statement was
right. I decided to look into other areas, and that's when I realized
my mistake about my table layout - too many darn columns! So your
input did really help me a lot - otherwise I might still be looking
for ways to change my BULK insert statement -

Thanks to everybody for their input - I'm also going to post this
message into the other 1 or 2 groups that i posted to:

thanks again to everyone! "

 
 
 

1. BULK INSERT only inserting every 2nd Row

Hi -
I don't have a whole lot of experience with using BULK INSERT, and am
running into a problem that should have a very simple solution (i
think), but I'm apparently just not getting it. Here's what i have:

Bulk Insert Statement:

BULK INSERT [addresses]
From 'C:\MYTEXTFILE.txt'
WITH
(
FIELDTERMINATOR = '\n',
ROWTERMINATOR = '\n',
MAXERRORS = 0
)

Sample data from MYTEXTFILE.txt:
Username1
Username2
Username3
Username4
Username5
Username6
Username7
Username8
Username9
Username10

wHAT IS BEING RETURNED NOW:
Username2
Username4
Username6
Username8
Username10

I was thinking it had something to do with specifiying a
"FIELDTERMINATOR" when I really only have ONE field - My first thought
was to completely remove FIELDTERMINATOR, and just use
ROWTERMINATOR...but this threw errors - The command DOES run
successfully, but each time it Inserts exactly HALF the number of
records that are in my source text file.

2. Floats in SQL Server

3. Bulk Insert only inserting every 2nd row

4. Having problem in ms access query

5. Bulk insert Successfully but not row inserted

6. Sybase IQ 11.5 and ODBC 2.12.00007

7. Error when bulk insert follows another large bulk insert

8. T or F Borland Bankruptcy

9. INSERT TRIGGER----Is it possible to fire an Insert Trigger for every row in a multi-row insert?

10. Peforming a Exec on every Inserted Row

11. Insert Trigger with Bulk Insert

12. Insert Trigger on a bulk inserted table

13. ODBC- SQLParamOptions (bulk insert) not inserting