bcp errors.

I am trying to use bcp to copy some common columns from one table into
another table. Each table contains columns which will be ignored. I have
never seen a real-world example of bcp, and the only bcp examples I have seen
in MSDN are trivial, happy-path examples which do not address real-world
situations like this.
Do I have to account for each field in the text file? In the target table?
Here is what I have so far:
bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb -T
bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T -f
BCPformat.txt -k
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
BCPformat.txt
8.0
27
1 SQLINT 1 12 "\t" 88 NQUserId
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 50 "\t" 2 UserFirst
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 50 "\t" 6 Address
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_CI_AS
17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 2 200 "\t" 79 Type
SQL_Latin1_General_CP1_CI_AS
19 SQLBIT 1 1 "\t" 27 nomail
SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 2 50 "\t" 31 CoBra
SQL_Latin1_General_CP1_CI_AS
21 SQLBIT 1 1 "\t" 37 optout
SQL_Latin1_General_CP1_CI_AS
22 SQLTINYINT 1 5 "\t" 80 lowData
SQL_Latin1_General_CP1_CI_AS
23 SQLTINYINT 1 5 "\t" 81 AgentLead
SQL_Latin1_General_CP1_CI_AS
24 SQLBIT 1 1 "\t" 82 Membership
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_CI_AS
26 SQLBIT 1 1 "\t" 84 EmailSent
SQL_Latin1_General_CP1_CI_AS
27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
SQL_Latin1_General_CP1_CI_ASWhy can't you just run a sql statement, or wrap the sql in a stored
procedure...
INSERT Users_mjl
SELECT col1, col3, col5, col6, col7
FROM NotQuiteUsers
Snake wrote:
> I am trying to use bcp to copy some common columns from one table into
> another table. Each table contains columns which will be ignored. I have
> never seen a real-world example of bcp, and the only bcp examples I have seen
> in MSDN are trivial, happy-path examples which do not address real-world
> situations like this.
> Do I have to account for each field in the text file? In the target table?
> Here is what I have so far:
> bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb -T
> bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T -f
> BCPformat.txt -k
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>
> BCPformat.txt
> 8.0
> 27
> 1 SQLINT 1 12 "\t" 88 NQUserId
> SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 2 50 "\t" 2 UserFirst
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 2 50 "\t" 6 Address
> SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
> 12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_AS
> 13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI_AS
> 14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_AS
> 15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_CI_AS
> 17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 2 200 "\t" 79 Type
> SQL_Latin1_General_CP1_CI_AS
> 19 SQLBIT 1 1 "\t" 27 nomail
> SQL_Latin1_General_CP1_CI_AS
> 20 SQLCHAR 2 50 "\t" 31 CoBra
> SQL_Latin1_General_CP1_CI_AS
> 21 SQLBIT 1 1 "\t" 37 optout
> SQL_Latin1_General_CP1_CI_AS
> 22 SQLTINYINT 1 5 "\t" 80 lowData
> SQL_Latin1_General_CP1_CI_AS
> 23 SQLTINYINT 1 5 "\t" 81 AgentLead
> SQL_Latin1_General_CP1_CI_AS
> 24 SQLBIT 1 1 "\t" 82 Membership
> SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_CI_AS
> 26 SQLBIT 1 1 "\t" 84 EmailSent
> SQL_Latin1_General_CP1_CI_AS
> 27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
> SQL_Latin1_General_CP1_CI_AS|||If you are moving data from one table to another table in the same instance,
what Corey suggested is the right way to go. However, if you are moving data
to a new instance of SQL Server, here is what you can do
(1) Use QUERY feature of BCP-out to only export the columns that you want.
(2) Use Format file to skip columns or fields in the data file when
importing data file.
If you look at SQL2000 BOL, it does provide you info on how to use data file
with fewer fields, different order or more fields than the target column
thanks,
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1132072636.728960.229590@.g47g2000cwa.googlegroups.com...
> Why can't you just run a sql statement, or wrap the sql in a stored
> procedure...
> INSERT Users_mjl
> SELECT col1, col3, col5, col6, col7
> FROM NotQuiteUsers
>
> Snake wrote:
>> I am trying to use bcp to copy some common columns from one table into
>> another table. Each table contains columns which will be ignored. I
>> have
>> never seen a real-world example of bcp, and the only bcp examples I have
>> seen
>> in MSDN are trivial, happy-path examples which do not address real-world
>> situations like this.
>> Do I have to account for each field in the text file? In the target
>> table?
>> Here is what I have so far:
>> bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S
>> ev\dweb -T
>> bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S
>> dev\dweb -T -f
>> BCPformat.txt -k
>> Starting copy...
>> SQLState = 22001, NativeError = 0
>> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>>
>> BCPformat.txt
>> 8.0
>> 27
>> 1 SQLINT 1 12 "\t" 88 NQUserId
>> SQL_Latin1_General_CP1_CI_AS
>> 2 SQLCHAR 2 50 "\t" 2 UserFirst
>> SQL_Latin1_General_CP1_CI_AS
>> 3 SQLCHAR 2 50 "\t" 3 UserLast
>> SQL_Latin1_General_CP1_CI_AS
>> 4 SQLCHAR 2 50 "\t" 4 Company
>> SQL_Latin1_General_CP1_CI_AS
>> 5 SQLCHAR 2 50 "\t" 5 SponsorId
>> SQL_Latin1_General_CP1_CI_AS
>> 6 SQLCHAR 2 50 "\t" 6 Address
>> SQL_Latin1_General_CP1_CI_AS
>> 7 SQLCHAR 2 50 "\t" 7 City
>> SQL_Latin1_General_CP1_CI_AS
>> 8 SQLCHAR 2 50 "\t" 8 State
>> SQL_Latin1_General_CP1_CI_AS
>> 9 SQLCHAR 2 50 "\t" 9 Zip
>> SQL_Latin1_General_CP1_CI_AS
>> 10 SQLCHAR 2 50 "\t" 10 Phone
>> SQL_Latin1_General_CP1_CI_AS
>> 11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
>> 12 SQLCHAR 2 50 "\t" 12 Email
>> SQL_Latin1_General_CP1_CI_AS
>> 13 SQLCHAR 2 50 "\t" 78 Subscription
>> SQL_Latin1_General_CP1_CI_AS
>> 14 SQLCHAR 2 40 "\t" 18 HeardFrom
>> SQL_Latin1_General_CP1_CI_AS
>> 15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
>> 16 SQLCHAR 2 50 "\t" 20 Profession
>> SQL_Latin1_General_CP1_CI_AS
>> 17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
>> 18 SQLCHAR 2 200 "\t" 79 Type
>> SQL_Latin1_General_CP1_CI_AS
>> 19 SQLBIT 1 1 "\t" 27 nomail
>> SQL_Latin1_General_CP1_CI_AS
>> 20 SQLCHAR 2 50 "\t" 31 CoBra
>> SQL_Latin1_General_CP1_CI_AS
>> 21 SQLBIT 1 1 "\t" 37 optout
>> SQL_Latin1_General_CP1_CI_AS
>> 22 SQLTINYINT 1 5 "\t" 80 lowData
>> SQL_Latin1_General_CP1_CI_AS
>> 23 SQLTINYINT 1 5 "\t" 81 AgentLead
>> SQL_Latin1_General_CP1_CI_AS
>> 24 SQLBIT 1 1 "\t" 82 Membership
>> SQL_Latin1_General_CP1_CI_AS
>> 25 SQLCHAR 2 1 "\t" 83 MemberType
>> SQL_Latin1_General_CP1_CI_AS
>> 26 SQLBIT 1 1 "\t" 84 EmailSent
>> SQL_Latin1_General_CP1_CI_AS
>> 27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
>> SQL_Latin1_General_CP1_CI_AS
>|||Sunil Agarwal [MSFT] wrote:
> If you are moving data from one table to another table in the same
> instance, what Corey suggested is the right way to go. However, if
> you are moving data to a new instance of SQL Server, here is what you
> can do (1) Use QUERY feature of BCP-out to only export the columns
> that you want. (2) Use Format file to skip columns or fields in the
> data file when importing data file.
Additional options:
- DTS
- link instances
Kind regards
robert