Execute the following script to create a staging table and import (BULK INSERT) a flat file into it numbering each row with the identity(1,1) sequence.

USE AdventureWorks2008;

CREATE TABLE #stgImportFlatfile (

Record  varchar(256) null) 

GO

CREATE TABLE stgImportFlatfile (

ID int identity(1,1) primary key,

Record  varchar(256) null) 

GO

BULK INSERT #stgImportFlatfile

FROM 'F:\data\import\ProductBulk.txt'

GO

DELETE #stgImportFlatfile WHERE Record is null

GO

INSERT stgImportFlatfile(Record)

SELECT * FROM #stgImportFlatfile

GO

SELECT * from stgImportFlatfile

GO

DROP TABLE #stgImportFlatfile

GO

-- DROP TABLE stgImportFlatfile

GO