Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!.POSTED!not-for-mail From: stukie Newsgroups: comp.databases.ms-sqlserver Subject: Re: BCP ordering? Date: Fri, 17 Jun 2011 06:53:33 +0000 (UTC) Organization: Aioe.org NNTP Server Lines: 68 Message-ID: References: <8d9b7401-207c-4c90-bac1-c99f98783b3b@r2g2000vbj.googlegroups.com> NNTP-Posting-Host: 2AAxi9HIhdDqAG3Tszy88g.user.speranza.aioe.org Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@aioe.org User-Agent: Pan/0.133 (House of Butterflies) X-Notice: Filtered by postfilter v. 0.8.2 Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:463 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.