How to get around error 8152?

Question

How to get around error 8152?

Michael Beane

Valued Member

Points: 66
More actions
March 14, 2005 at 5:08 pm

#162899

I am trying to copy records to an archive table and am getting error 8152, "String or binary data would be truncated." The two tables are identical, except one column is char(3) in the base table and char(2) in the archive table. (Not my design! ) Here is the command I am using, in simplified format. The Period column is the one that differs between the two tables.
insert into ArchiveTable
SELECT field1, field2, SUBSTRING(period,2,2) AS PERIOD, field4
FROM BaseTable
I thought grabbing only 2 characters of the Period field would create a 2 character column in the SELECT result set, but apparently not.
Any help appreciated!

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

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

philcart SSC-Forever Points: 47797 More actions · Answer 1

You can check the datatype returned from the substring by executing a short SELECT ... INTO test.

SELECT  TOP 10 
    field1, field2, SUBSTRING(period,2,2) AS PERIOD, field4 
into TestTable
FROM BaseTable

You'll probably need to cast the substring into a varchar.

--------------------
Colt 45 - the original point and click interface

Michael Beane Valued Member Points: 66 More actions · Answer 2

I have tried casting the expression every way I can think of, and no joy. The Period column is defined as CHARACTER(3) in BaseTable and CHARACTER(2) in ArchiveTable. Can you help me with the CAST() syntax needed?

Can't get INTO to work, either. My login ID has all permissions on this database, including CREATE, but I get an error message saying "Invalid syntax near INTO."

This is driving me nuts. Time to call it a day!

TIA,

Mike (looking very much like the guy banging his head on the desk in your logo)

philcart SSC-Forever Points: 47797 More actions · Answer 3

Hmmm ... must be something else here. Just ran a little test myself,

CREATE Table tt_basetable (
 field1 int
 , field2 int
 , field3 char(3)
 , field4 int
 , field5 int
)

CREATE Table tt_arctable (
 field1 int
 , field2 int
 , field3 char(2)
 , field4 int
 , field5 int
)

INSERT INTO tt_basetable VALUES (1, 2, 'abc', 3, 4)
INSERT INTO tt_basetable VALUES (1, 2, 'def', 3, 4)
INSERT INTO tt_basetable VALUES (1, 2, 'ghi', 3, 4)
INSERT INTO tt_basetable VALUES (1, 2, 'jkl', 3, 4)
INSERT INTO tt_basetable VALUES (1, 2, 'mno', 3, 4)

INSERT INTO tt_arctable
SELECT 
 field1
 , field2
 , SUBSTRING(field3, 2, 2) as field3
 , field4
 , field5
FROM tt_basetable

Results
1 2 bc 3 4
1 2 ef 3 4
1 2 hi 3 4
1 2 kl 3 4
1 2 no 3 4

--------------------
Colt 45 - the original point and click interface

Michael Beane Valued Member Points: 66 More actions · Answer 4

Phil,

You're right, there has to be something else going on. That all works fine and is the same thing I think my code is doing, just simplified. Obviously I am missing something .... back to debugging. I appreciate your help.

Mike

Michael Beane Valued Member Points: 66 More actions · Answer 5

Phil,

I found my problem -- the goofy character at the front of the keyboard! The problem was I didn't have the fields in the right order. Unbelievably stupid, but I wasn't even paying attention to that because I was focusing on the formatting of the Period column.

You know how sometimes you don't mind feeling like a dumba*s because you're glad the problem is solved? It's like that.

Thanks again for your help!

Mike

philcart SSC-Forever Points: 47797 More actions · Answer 6

No problem. It's always a pleasure to help with the simple problems

--------------------
Colt 45 - the original point and click interface