f4e9fff1   
   From: stukieh@gmail.com   
      
   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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)