Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #463

Re: BCP ordering?

Show all headers | View raw

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

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

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

BCP ordering? blindsey <blindsey@dsicdi.com> - 2011-06-10 06:18 -0700
  Re: BCP ordering? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-11 00:05 +0200
  Re: BCP ordering? stukie <stukieh@gmail.com> - 2011-06-17 06:53 +0000
  Re: BCP ordering? Gene Wirchenko <genew@ocis.net> - 2011-06-17 12:15 -0700
    Re: BCP ordering? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-17 23:35 +0200
      Re: BCP ordering? Gene Wirchenko <genew@ocis.net> - 2011-06-17 15:25 -0700
        Re: BCP ordering? Erland Sommarskog <esquel@sommarskog.se> - 2011-06-18 11:29 +0200

csiph-web