Very Computer

by **Christopher Klei** » Thu, 11 Oct 2001 03:02:48

hey there,

I have a simple table
intID IDENTITY
vchrMAP varchar
vchrBLOCK varchar
vchrLOT varchar

I'm using thru Query Analyzer the following:
bulk insert temptest from 'z:\book1.txt' with (formatfile='z:\test.fmt')

the formatfile is:
8.0
3
1 SQLCHAR 0 10 "," 2 vchrMAP SQL_Latin1_General_Cp1_CI_AS
2 SQLCHAR 0 10 "," 3 vchrBLOCK SQL_Latin1_General_Cp1_CI_AS
3 SQLCHAR 0 10 "\r\n" 4 vchrLOT SQL_Latin1_General_Cp1_CI_AS

and sample data from book1.txt is
201,1,0
201,2,0
201,3,0
201,4,0
201,5,0
201,6,0

book1.txt contains 2086 records.

When I run the procedure it runs fine without errors. The column data is
inserted into the proper fields HOWEVER the identity column which should
start at 1 and increment by 1 sporadically increments by different numbers.
Has anyone ever seen this before and can someone please tell me I just
forgot some setting to fix this? I need to import alot of data and having a
consistent numbering scheme is sorta what I need and I would rather NOT do
it myself.

Regards,

Christopher Klein
Point Software, Inc.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

by **Zachary Well** » Thu, 11 Oct 2001 03:11:30

If you need to guarantee sequence then manually insert the number using
identity_insert. On a related note, if your data is require to be sequential
then IDENTITY is usually NOT a good solution!

Zach

> hey there,

> I have a simple table
> intID IDENTITY
> vchrMAP varchar
> vchrBLOCK varchar
> vchrLOT varchar

> I'm using thru Query Analyzer the following:
> bulk insert temptest from 'z:\book1.txt' with (formatfile='z:\test.fmt')

> the formatfile is:
> 8.0
> 3
> 1 SQLCHAR 0 10 "," 2 vchrMAP SQL_Latin1_General_Cp1_CI_AS
> 2 SQLCHAR 0 10 "," 3 vchrBLOCK SQL_Latin1_General_Cp1_CI_AS
> 3 SQLCHAR 0 10 "\r\n" 4 vchrLOT SQL_Latin1_General_Cp1_CI_AS

> and sample data from book1.txt is
> 201,1,0
> 201,2,0
> 201,3,0
> 201,4,0
> 201,5,0
> 201,6,0

> book1.txt contains 2086 records.

> When I run the procedure it runs fine without errors. The column data is
> inserted into the proper fields HOWEVER the identity column which should
> start at 1 and increment by 1 sporadically increments by different
numbers.
> Has anyone ever seen this before and can someone please tell me I just
> forgot some setting to fix this? I need to import alot of data and having
a
> consistent numbering scheme is sorta what I need and I would rather NOT do
> it myself.

> Regards,

> Christopher Klein
> Point Software, Inc.

> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----

by **Christopher Klei** » Thu, 11 Oct 2001 03:29:32

it doesn't need to be sequential so much as I'm more curious as to WHY its
happening
'set identity_insert temptest on' didn't really seem to do anything since
the intID numbers
being generated still jump around.
I'm still a tad new at the SQL scene but shouldnt an identity column set to
start at 1 and increment
by 1 do that? its almost like this is some sort of buffer overflow and its
just writing the numbers as it gets to them.
If I have to manually deal with the intID numbers I will that's not a big
issue I'm really just curious.

using SQL2000 btw

thanks for the reply

regards,
Chris

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

by **Charles Bretana Jr** » Thu, 11 Oct 2001 03:38:13

Although this may not satisfy your question, the reason is that it would
have been MUCH harder to implement this capability in such a manner as to
guarantee Sequentiality, and the performance impact would have been much
more severe. The reasons for this are related to the requirement to
interact with EVERY Other process that might be inserting values at the same
time your process is inserting values. So, since there is no obvious
requirement for sequentiality, only for uniqueness, they did not bother to
add this capability...

Quote:> it doesn't need to be sequential so much as I'm more curious as to WHY its
> happening
> 'set identity_insert temptest on' didn't really seem to do anything since
> the intID numbers
> being generated still jump around.
> I'm still a tad new at the SQL scene but shouldnt an identity column set
to
> start at 1 and increment
> by 1 do that? its almost like this is some sort of buffer overflow and
its
> just writing the numbers as it gets to them.
> If I have to manually deal with the intID numbers I will that's not a big
> issue I'm really just curious.

> using SQL2000 btw

> thanks for the reply

> regards,
> Chris

> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----

by **Zachary Well** » Thu, 11 Oct 2001 03:42:45

I've never encountered this so I can't really give you an answer as to why
its happening. Sorry.

Zach

Quote:> it doesn't need to be sequential so much as I'm more curious as to WHY its
> happening
> 'set identity_insert temptest on' didn't really seem to do anything since
> the intID numbers
> being generated still jump around.
> I'm still a tad new at the SQL scene but shouldnt an identity column set
to
> start at 1 and increment
> by 1 do that? its almost like this is some sort of buffer overflow and
its
> just writing the numbers as it gets to them.
> If I have to manually deal with the intID numbers I will that's not a big
> issue I'm really just curious.

> using SQL2000 btw

> thanks for the reply

> regards,
> Chris

> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----

by **Christopher Klei** » Thu, 11 Oct 2001 04:00:25

okies, I feel pretty stupid.
figuring when you have a table intID,map,block,lot when you do a simple
select * from temptest
that would be the order of them...
forgot ORDER BY.

Sorry for being an idiot but thanks for the help... would of took me a bit
to figure what was obviously starring me in the face.

regards,

Chris

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----

by **Keith Wo** » Fri, 12 Oct 2001 06:32:18

Chris,
What service pack are you on? Is it skipping by the same amount each time the problem is seen?
For testing purpose, can you try with BCP?
-Keith

=========================
* This posting is provided "AS IS" with no warranties, and confers no rights.
* You assume all risk for your use. ? 2001 Microsoft Corporation. All rights reserved
*
* Please do not send email directly to this alias.
=========================
--------------------

|>Newsgroups: microsoft.public.sqlserver.programming
|>Subject: BULK insert not giving sequential IDENTITY numbers to a field
|>Date: Tue, 9 Oct 2001 14:02:48 -0400
|>Lines: 44

|>hey there,
|>
|>I have a simple table
|>intID IDENTITY
|>vchrMAP varchar
|>vchrBLOCK varchar
|>vchrLOT varchar
|>
|>I'm using thru Query Analyzer the following:
|>bulk insert temptest from 'z:\book1.txt' with (formatfile='z:\test.fmt')
|>
|>the formatfile is:
|>8.0
|>3
|>1 SQLCHAR 0 10 "," 2 vchrMAP SQL_Latin1_General_Cp1_CI_AS
|>2 SQLCHAR 0 10 "," 3 vchrBLOCK SQL_Latin1_General_Cp1_CI_AS
|>3 SQLCHAR 0 10 "\r\n" 4 vchrLOT SQL_Latin1_General_Cp1_CI_AS
|>
|>and sample data from book1.txt is
|>201,1,0
|>201,2,0
|>201,3,0
|>201,4,0
|>201,5,0
|>201,6,0
|>
|>book1.txt contains 2086 records.
|>
|>When I run the procedure it runs fine without errors. The column data is
|>inserted into the proper fields HOWEVER the identity column which should
|>start at 1 and increment by 1 sporadically increments by different numbers.
|>Has anyone ever seen this before and can someone please tell me I just
|>forgot some setting to fix this? I need to import alot of data and having a
|>consistent numbering scheme is sorta what I need and I would rather NOT do
|>it myself.
|>
|>Regards,
|>
|>Christopher Klein
|>Point Software, Inc.

|>
|>
|>
|>
|>
|>-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
|>http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
|>-----== Over 80,000 Newsgroups - 16 Different Servers! =-----
|>

by **Steve Kas** » Fri, 12 Oct 2001 07:27:08

The default ERRORCOUNT for BULK INSERT is 10, meaning up to 10 records will
be skipped (silently) if they can't be imported. But I would expect a new
identity value to be generated for every insertion attempt, so corrupt
records would cause skips (handy to know if it turns out to be the case) -
do you end up with as many records in your table as there are in the
original data?

Steve Kass
Drew University

Quote:> Chris,
> What service pack are you on? Is it skipping by the same amount each time

the problem is seen?

> For testing purpose, can you try with BCP?
> -Keith

> =========================
> * This posting is provided "AS IS" with no warranties, and confers no
rights.
> * You assume all risk for your use. ? 2001 Microsoft Corporation. All
rights reserved
> *
> * Please do not send email directly to this alias.
> =========================
> --------------------

> |>Newsgroups: microsoft.public.sqlserver.programming
> |>Subject: BULK insert not giving sequential IDENTITY numbers to a field
> |>Date: Tue, 9 Oct 2001 14:02:48 -0400
> |>Lines: 44

> |>hey there,
> |>
> |>I have a simple table
> |>intID IDENTITY
> |>vchrMAP varchar
> |>vchrBLOCK varchar
> |>vchrLOT varchar
> |>
> |>I'm using thru Query Analyzer the following:
> |>bulk insert temptest from 'z:\book1.txt' with (formatfile='z:\test.fmt')
> |>
> |>the formatfile is:
> |>8.0
> |>3
> |>1 SQLCHAR 0 10 "," 2 vchrMAP SQL_Latin1_General_Cp1_CI_AS
> |>2 SQLCHAR 0 10 "," 3 vchrBLOCK SQL_Latin1_General_Cp1_CI_AS
> |>3 SQLCHAR 0 10 "\r\n" 4 vchrLOT SQL_Latin1_General_Cp1_CI_AS
> |>
> |>and sample data from book1.txt is
> |>201,1,0
> |>201,2,0
> |>201,3,0
> |>201,4,0
> |>201,5,0
> |>201,6,0
> |>
> |>book1.txt contains 2086 records.
> |>
> |>When I run the procedure it runs fine without errors. The column data
is
> |>inserted into the proper fields HOWEVER the identity column which should
> |>start at 1 and increment by 1 sporadically increments by different
numbers.
> |>Has anyone ever seen this before and can someone please tell me I just
> |>forgot some setting to fix this? I need to import alot of data and
having a
> |>consistent numbering scheme is sorta what I need and I would rather NOT
do
> |>it myself.
> |>
> |>Regards,
> |>
> |>Christopher Klein
> |>Point Software, Inc.

> |>
> |>
> |>
> |>
> |>
> |>-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> |>http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> |>-----== Over 80,000 Newsgroups - 16 Different Servers! =-----
> |>

by **Keith Wo** » Sat, 13 Oct 2001 04:25:32

Yes, it should increment by 1 by default.
Do you see the problem with BCP? Is this SQL 2K with SP1?
-Keith