Can I generate an identity column when importing using BCP that will
sequence by the physical order of the records?

blindsey (blindsey (AT) dsicdi (DOT) com) writes:
I was told by SQL Server developers that you cannot rely on this. So I submitted a Connect item to call for this:
http://connect.microsoft.com/SQLServ...loading-a-file

And I was told, that order is in fact guaranteed.

To be honest, I don't know what to believe. Try it, but keep your eyes open.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Fri, 10 Jun 2011 06:18:07 -0700, blindsey wrote:

My results are good when the input file has a sequence number that the
identity column can use.

Notice that the identity column will be honored even when subsequent
files are loaded, each starting at sequence 1.

given the following table:

CREATE TABLE [dbo].[Table_1](
[bcp_id] [bigint] IDENTITY(1,1) NOT NULL,
[fieldNumber1] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[bcp_id] ASC
)WITH
( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and a tab-delimited import file c:\bcp\test.txt

1 record-from-file-1
2 record-from-file-2

the following bcp command will load the records in that sequence:

c:\>bcp dbo.table_1 in c:\bcp\test.txt -SSQLServerName -T -c

Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 94 Average : (21.28 rows per sec.)

select * from table_1 would list:

bcp_id fieldNumber1
------ ------------------
1 record-from-file-1
2 record-from-file-2

(2 row(s) affected)

loading test1.txt as

1 record-from-file-4
2 record-from-file-3

would result in:

bcp_id fieldNumber1
------ ------------------
1 record-from-file-1
2 record-from-file-2
3 record-from-file-4
4 record-from-file-3

(4 row(s) affected)

of course, all caveats apply.

On Fri, 10 Jun 2011 06:18:07 -0700 (PDT), blindsey
<blindsey (AT) dsicdi (DOT) com> wrote:

What physical order?

A file can be in chunks all over a volume. If you mean within
the file, the DBMS could write a row anywhere it has space to do so.

You have to define an order if you want one.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Gene, he is talking about reading a text file into SQL Server with
the BCP command. Text files typically have a physical order. Well,
if you want to talk disk blocks, that's really a logical order. But
ordered they are.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Fri, 17 Jun 2011 23:35:12 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Not my point. 1) He is then expecting them to be in order in the
table. 2) He gives SELECTs with no ORDER BY clauses and expects an
order.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
All he asked for was whether the IDENTITY column would sequence by the
data in the file. That is certainly a very valid request, since there
may be dependecies between the data.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx