problem with sizes

Question

Post reply

problem with sizes

VRT

SSCertifiable

Points: 5776
More actions
January 29, 2009 at 4:12 am

#128278

Hi,
I just want to know what happens if the data size and the fields size mismatches, while inserting into table.
case 1: data < Field size (20 bytes < 50 bytes)
case 2: data > Field size (100 bytes > 50 bytes)
case 3: data = Field size (50 bytes = 50 bytes)
Please let me know this... I have a trouble with one of the stored procedure that takes the data from one table and insert into another?
Thanks in advance,
Venki.
Thank You

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

If the data size is to big for the column then the insert will fail.

Otherwise if is the the same size or less then it will succeed.

This does depend on what the datatye is though.

VRT SSCertifiable Points: 5776 More actions · Answer 2

steveb (1/29/2009)
If the data size is to big for the column then the insert will fail.
Otherwise if is the the same size or less then it will succeed.
This does depend on what the datatye is though.

Thanks for your reply Steve.

My problem here is... I am transferring the data from one table to another table.

All source table columns are of 200 bytes length.

But Destination table columns are of different length. For some of the columns it was 50 bytes, for others 200 and for some other columns 255 bytes.

The insertion failed by the error__

String or binary data would be truncated.

What might be the reason?

Thank You

Vladan SSC-Insane Points: 22072 More actions · Answer 3

When inserting strings, you have to handle their length.

If you know that target column is VARCHAR(20), and the original text can sometimes be longer than that, then you have several options:

- change the definition of source to have 20 characters

- change the definition of target column, so that it is long enough

- use string function (LEFT, RIGHT, SUBSTRING) to shorten the inserted value to length of 20 characters

You could also parse the string and insert it into several rows, or create a special table that will hold the full length while you only insert first 20 characters into target table (e.g. because that table can't be modified and at the same time you don't want to lose any data).

Solution depends on what data it is, what you can change in tables, whether loss of part of the string is acceptable etc.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

You are better off doing a comparison of the source and destination tables and check the differences. Then plan your data transfer.

"Keep Trying"

VRT SSCertifiable Points: 5776 More actions · Answer 5

Thanks a lot for your valuable suggestions.

I am checking the data and sizes and adjust accordingly.

Best wishes,

Venki.

Thank You

VRT SSCertifiable Points: 5776 More actions · Answer 6

Hi,

I got a small doubt. Let me explain the process and then my doubt.

I am tranferring the data from couple of tables to another table.

I am using temporary tables called staging tables in between source and destination for the purpose of mailing.

Here, process is as the first SP is taking the source table data and changing the some columns to integers and date types and inserting into staging table. But all columns in staging table are varchar data type.

Again, another SP taking these staging table data and inserts into destination table. But some of the columns in destination table are integer and date data types.

So ultematly, in the second step, the varchar data is converting to integer and date formats.

my doubt here is that is it fine that varchar to integer conversion? will it be a problem for insertion? I am wondering why it is not showing any error for this conversion? is Inernal conversion happening here? How come varchar to integer is happening?

Thank You