Truncating a varchar for a migration

[mateobus]

Hello, I am migrating some data from one table to another table. One of the columns in both tables is for phone numbers. In the original table its a varchar(50) and in the other its a varchar(20). When I try to do the migration, I am getting an error when there is a phone number longer than 20 chars. Is there a way to truncate the ones longer than 20 using t-sql? Thanks in advance.

matt

 

[chamilz]

how about this?

Select *
from TableA
where LEN(PhoneNumber) < = 20

 

[onpnt]

That will only bring over the data that meets the limits of course. Are you sure the data is not required? Has the business stated this is a valid loss?

If you want to bring the values over you can convert the data in the transform. This will trunc it to what you convert it to.

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[mateobus]

Yes, I would like to convert the data in the transform, who do i do that?

 

[onpnt]

I gave you that answer. "Convert"

You need to be more specific if you want detail help in how you are transferring your data otherwise. At that point if this is not a T-SQL question then you need to ask in teh DTS or SSIS forums. Otherwsie the Convert answers it

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[chamilz]

Has the business stated this is a valid loss?

Yes,that's a valid question. The query posted above limits the data. There is a way to prevent that data loss.

1.Copy all the data except "PhoneNumber" to the NewTable.

2. Then update the Phonenumber of NewTable from the OldTable where the PhoneNmber is equal or less than 20 chars. i.e. LEN(PhoneNumber) < = 20.

Make sure that both tables have unique keys.

 

[mateobus]

ok, thanks. I didn't realize that convert was a real keyword.