2012年2月18日星期六

BCP - Skip the first column in a BCP opperation

How can I skip the first column in a BCP operation. My data file does not contain the data for the first column. From the sample below I want to skip the column named GCRecord.

Table schema [StopOrderCode]

[GCRecord] int NULL,
[Id] int NOT NULL,
[EmployerPayCode] nvarchar(100) NULL,
[EmployerName] nvarchar(100) NULL,
[EnglishEmployerName] nvarchar(100) NULL,
[AfrikaansEmployerName] nvarchar(100) NULL,
[IsGovernmentCode] bit NULL

Format file

9.0
6
1 SQLINT "" 4 "\t" 2 Id ""
2 SQLNCHAR "" 200 "\t" 3 EmployerPayCode ""
3 SQLNCHAR "" 200 "\t" 4 EmployerName Latin1_General_CI_AS
4 SQLNCHAR "" 200 "\t" 5 EnglishEmployerName Latin1_General_CI_AS
5 SQLNCHAR "" 200 "\t" 6 AfrikaansEmployerName Latin1_General_CI_AS
6 SQLBIT "" 1 "\r\n" 7 IsGovernmentCode

Data sample (Tab deliminated)

Id EmployerPayCode EmployerName EnglishEmployerName AfrikaansEmployerName IsGovernmentCode
676 9271 Abakor Bpk Abakor Bpk Abakor Bpk 0
837 9639 Aberdare Telecom Division Aberdare Telecom Division Aberdare Telecom Division 0

BCP statement


bcp DBName.dbo.StopOrderCode in ".\Table Data\StopOrderCode.txt" -S DBServer\InstanceName -T -f ".\Table Data\StopOrderCode.fmt" -C ACP -b 1000 -F 2

I am using sql 2005.

Thanks in advance

When the datafile contains more columns than the table,

bcp into a 'Staging' table that maps to the data file, and then copy appropriate columns to the final table.

When the table has more columns than the datafile,

CREATE a VIEW that maps the data to the table, and bcp into the VIEW.

没有评论:

发表评论