bcp

Question

Post reply

bcp

john.priest

SSC Veteran

Points: 235
More actions
January 26, 2010 at 6:01 pm

#103889

I am trying to bcp some data from one database to another via bcp (i know i can use other methods but is tarted using this as a self-training mechanism).
An issue comes when I try to import the data into the new database table. The issue concerns the fact that the table(s) have a column called key.
If I try to import the data I get the following error message
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'key'.
I have several hundred tables with a column called key. Is there any way to overcome this issue without re-naming every key column to skey (or something).
Cheers
JP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 1

Can we see the BCP command, please, John? Be sure to remember to not include your server password. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

john.priest SSC Veteran Points: 235 More actions · Answer 2

bcp command:

"C:\PROGRAM FILES\MIcrosoft SQL Server\80\Tools\Binn\bcp" ie_master.dbo.D_BillTypes out Y:\bcptest\D_BillTypes.txt -S******** -Uds_user -P******* -fY:\bcptest\D_BillTypes.fmt

table definition:

USE ie_master

go

CREATE TABLE dbo.D_BillType

(

BTC_BILL_TYPE_CODE char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

BTC_BILL_TYPE_DESCRIPTION char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

BTC_EXPIRY_DATE datetime NULL,

int NOT NULL,

CONSTRAINT PK_D_BillType

PRIMARY KEY NONCLUSTERED ()

WITH FILLFACTOR=90 ON IndexFileGroup

)

ON IndexFileGroup

go

IF OBJECT_ID(N'dbo.D_BillType') IS NOT NULL

PRINT N'<<< CREATED TABLE dbo.D_BillType >>>'

ELSE

PRINT N'<<< FAILED CREATING TABLE dbo.D_BillType >>>'

go

.fmt definition

8.0

4

1 SQLCHAR 0 3 "~" 1 BTC_BILL_TYPE_CODE SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 20 "~" 2 BTC_BILL_TYPE_DESCRIPTION SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 26 "~" 3 BTC_EXPIRY_DATE ""

4 SQLINT 0 4 "\r" 4 key ""

cheers

john