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.
没有评论:
发表评论