INSERT INTO from crosstab adds spaces

[AllanBertelsen]

I try to denormalize a Access database. Results from a crosstab query are loaded to a table by an "Insert into" statement. Other related data are loaded as well by the same statement.

The fields that comes from the crosstab are all char(25). If there is data present in one of these fields, the operation fills the field not only with the value but also spaces. Fields that is not from the crosstab does not get spaces added. These extra spaces are very annoying. So I hope someone can help.

 

[AllanBertelsen]

Finally got the solution
The problem is that when adding fields for the table using DDL I choose char(25). I now changed it to text(25). That solved the problem.

 

[PHV]

Use the Trim function in your INSERT INTO statement.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AllanBertelsen]

TRIM is not possible. You can never be sure about fieldnames from a crosstab. That is why I use SELECT * in my INSERT INTO statement. The statement has the following form:

INSERT INTO DestinationTable
SELECT qryDataFromCrostabAndOtherTabels.*
FROM qryDataFromCrostabAndOtherTabels

But the solution mentioned above is working.

Thanks anyway