bcp and hypens - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

bcp and hypens

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2004-01-06 : 12:14:37

I am having trouble with bcp and field names that contain hypens



create database testme
go

use testme
go

create table test(
	[hy-phen] int
)
go

insert into test
select 1
go

create table loadme(
	[hy-phen] int
)
go

--drop database testme

I then try and bcp using


bcp "testme..[test]" out "test.txt" -U"yourU" -P"yourP" -N -S yourdb
bcp "testme..[loadme]" in "test.txt" -U"yourU" -P"yourP" -N -S yourdb

Out works fine, but trying to insert the data results in an error message



SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '-'.

I am fairly certain it doesn't like the - in hy-pen, I have BoL'd and Google'd but I can't work out what switches to use to make this work..

Any help/hints appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-06 : 12:35:14

bcp the data into a staging table that does not contain dashes in the column names. Then use T-SQL to move the data from the staging table to the destination table.

Tara

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2004-01-06 : 13:00:35

I haven't been using bcp for a while, but here's a guess. Try your BCP IN, using an additional parameter -q and see if that helps.

--
HTH,
Vyas
http://vyaskn.tripod.com

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-06 : 13:05:04

Create a view on the table so that you can rename the columns and bcp into that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-06 : 14:41:09

quote:
Originally posted by nr

Create a view on the table so that you can rename the columns and bcp into that.

Now why didn't I think of that! uberman, this is probably the easiest way to do it if the -q option for bcp.exe doesn't work.

Tara

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2004-01-06 : 15:01:39

I still haven't tried it, but just had a look at BOL, and it doesn't look like -q is the solution.

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources