Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

bcp Expand / Collapse
Author
Message
Posted Tuesday, January 26, 2010 6:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 7:52 PM
Points: 115, Visits: 19
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
Post #854058
Posted Tuesday, January 26, 2010 11:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #854172
Posted Wednesday, January 27, 2010 7:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 7:52 PM
Points: 115, Visits: 19
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,
[key] int NOT NULL,
CONSTRAINT PK_D_BillType
PRIMARY KEY NONCLUSTERED ([key])
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
Post #854867
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse