Script to ammend column data

[kma]

Dear All,

I need to transfer data between two databases on two different SQL servers(both SQL server 2000).

I have a script that selects data from three tables on server1 and organises into the required format and stores in table A.

I do a bcp on table A and creates an output .txt file.

I use a bulk insert to table B. The error I get is a duplicate key violation and it comes from one column in table A which is a PK in table B.

I need to modify my script to alter data in column ID in table to say ID+a (e.g. 200 to 200a). ID is of datatype char. In this case I will not run into duplicate key problem with the bulk insert.

Thanks and regards
Kwame

[dorinbogdan]

If you need to update all records in table A, on column name ID, then use:

Update A set ID = ID + 'a'

[kma]

Thanks a lot but the suggested solution gives me IDa in all therows of the column. What I want is somthing like this

Table B

ID
2
3
4
5

Transforms to

Table B
2a
3a
4a
5a

Thanks

[kma]

Thanks a lot but the suggested solution gives me IDa in all therows of the ID column. What I want is somthing like this

Table B

ID
2
3
4
5

Transforms to

Table B

ID
2a
3a
4a
5a

Thanks

[dorinbogdan]

Use it exactly as it reads: ID = ID + 'a'
If run from Query Analyzer

Update B Set ID = ID + 'a'

it will update column ID as appears in your expected result.

[kma]

thanks doringbogdan

When I run as you have given I get the ff error

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Any help

Regards
Kwame