[RESOLVED] BCP program [MSSQL 2000]

[erickwidya]

i use this line in command prompt
BCP "SELECT * FROM Table1..Users" queryout "C:\file1.txt" -T -N

it use 'weird' character as field separator

i wonder if it can use '|' as separator?

i already read BOL but still can't understand completely how it done..

also can BCP be used using SQL DMO?

thanks,

erick

[brucevde]

The default field separator is a Tab. Use the -t argument to change the field separator.

BCP "SELECT * FROM Table1..Users" queryout "C:\file1.txt" -T -N -t"|"

[erickwidya]

thx brucevde
sorry for late reply..i didn't notice it must use -t for field separator..

i manage to do it and do the insert back from text file to table using BULK INSERT

here's the code

-- using query to specified selected data
BCP "SELECT * FROM DBNAME..TABLENAME" queryout "C:\FILENAME.txt" -T -w -k -t"|"

-- insert to a table from a file
BULK INSERT DBNAME..TABLENAME
FROM 'C:\FILENAME.txt'
WITH 
(
  FIELDTERMINATOR = '|',
  ROWTERMINATOR = '\n',
  DATAFILETYPE = 'widechar',
  KEEPNULLS
)

thanks again,
erick