>I am having trouble satisfying an unusual requirement which stems from poor data design (not mine!). What I need to do is copy records for the current period to an archive table. The two tables have identical structures, EXCEPT for one field which is char(3) in the base table and char(2) in the archive table. The insert is done as follows:
>
>INSERT INTO ?lcArchTable SELECT * FROM ?lcBaseTable
>
>This fails with the error message "String or binary data would be truncated." This is because it is trying to populate a 2 character column from a 3 character column. How can I get around this? I have tried doing an ALTER TABLE before the INSERT, to widen the column in the archive table to 3 characters, then altering it back to 2 characters afterwards. But the second ALTER fails with the same error message. (And even if it did work, I want the rightmost 2 characters, not the leftmost).
>
>I know I could use an explicit column list but there are actually several tables that have this same problem. It is all in a generic routine. I don't want to hard code columns of these particular tables and have to change code every time their structure changes. I was hoping there was something like "SELECT [all columns except ThisOne], RIGHT(ThisOne,2) AS ThisOne", but can find no such beast.
>
>Any ideas? TIA!
>
>Mike

Instead of SELECT *, you might explicitly list all the fields: SELECT Field1, Field2, left(Field3, 2) as Field3, assuming the problematic field is called Field3.