February 17, 2016 at 10:20 am
Is there a way to write my query so that it ignores truncation?
example: table 1 column A has data type varchar(8000) (I know crazy right?) but table 2 column A has data type varchar(50). I created table 2 but table 1 was created by our developers and I have no clue what they were thinking.
I need to insert what is in table 1 into table 2 and nothing in table 1 has anything longer than varchar(50) so how do I do it so that it inserts the data and does not fail on me?
Msg 8152, Level 16, State 14, Line 31
String or binary data would be truncated.
The statement has been terminated.
February 17, 2016 at 11:08 am
linda.russell (2/17/2016)
Is there a way to write my query so that it ignores truncation?example: table 1 column A has data type varchar(8000) (I know crazy right?) but table 2 column A has data type varchar(50). I created table 2 but table 1 was created by our developers and I have no clue what they were thinking.
I need to insert what is in table 1 into table 2 and nothing in table 1 has anything longer than varchar(50) so how do I do it so that it inserts the data and does not fail on me?
Msg 8152, Level 16, State 14, Line 31
String or binary data would be truncated.
The statement has been terminated.
SELECT LEFT(A, 50)
FROM Table1
February 17, 2016 at 11:31 am
djj's solution works, whether you have more or less:
DECLARE @a VARCHAR(100)
SELECT @a = 'aaa'
SELECT LEFT(@a, 50)
SELECT @a = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
SELECT LEFT(@a, 50)
However, if you truncate data, make sure clients know there is truncation occurring. I have found people get annoyed when data is shortened.
February 17, 2016 at 3:45 pm
In theory you could:
SET ANSI_WARNINGS OFF
But read up very, very carefully on that before you do it!
Or, you could create a stored proc and pass the values into the proc ... proc parameters always get truncated without any warnings at all.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply