Importing a text File into database

[WolverineLogan]

Hi All

Been trying to import an ordinary text file containing AccountNo's into our SQL Server 2000 database from the query
analyser.
Can anyone show me how to use the BCP utility ?
Could you give an example to please ?

Thanks

 

[mshetti]

Dear WolverineLogan

Take a look at this code, I import data from a text file the column delimiter is a pipe "|" and the rowdelimiter is "\n".

Create Table #Temp
( ClientName varchar(25) NULL ,
CategoryName varchar(15) NULL ,
InventoryNumber varchar(20) NULL ,
CustomerRefNo varchar(20) Null ,
Description varchar(255) Null ,
Descriptor varchar(50) NULL ,
Weight decimal(5, 0) Null ,
Height decimal(5, 0) NULL ,
Width decimal(5, 0) NULL ,
Depth decimal(5, 0) NULL ,
Comments varchar(500) Null )

-- Bulk copy the text file data into a table.
Select @SQL = 'Bulk Insert #Temp '
Select @SQL = @SQL + 'From ''' + @FileName + ''' '
Select @SQL = @SQL + 'With ( FIELDTERMINATOR = ''|'' , ROWTERMINATOR = ''\n'' , KEEPNULLS )'

Exec (@SQL)

Hope this helps...

Bye
Mahesh
mshetti@yahoo.com

 

[Jonax]

... or you could use DTS in EM This is not a bug - it's an undocumented feature...
;-)