How to ignore "String or binary data would be truncated"

Question

How to ignore "String or binary data would be truncated"

Viewing 3 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 1

<Head-Desk> I'm obviously sleeping. I total missed the fact that the history table has an active IDENTITY column in it. The column offset by using SELECT * to insert into the table without using column names is what is causing the truncation error because it's try to insert the wrong things into the wrong columns.

Thanks, Magoo.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

halifaxdal (1/16/2015)
Jeff Moden (1/16/2015)
Its fine the way you have it. If your history table has exactly the same schema and datatypes as the source table, then there's something very strange going on that's going to require a deeper dive on your part.
You can see my script is to create an exactly the same schema with only one extra field added which is ArchivedOn (to record the back up time)

And plus identity column in the target and no IDENTITY_INSERT in the script.

CREATE TABLE [dbo].[Eventlog](

[LogID] [int] IDENTITY(1,1) NOT NULL,

[Description] [nvarchar](max) NOT NULL,

[col2] [varchar](10) NOT NULL)

go

insert [dbo].[Eventlog] values('some text 10+ chars',10)

go

CREATE TABLE [dbo].[EventlogHistory](

[LogID] [int] IDENTITY(1,1) NOT NULL,

[Description] [nvarchar](max) NOT NULL,

[col2] [varchar](10) NOT NULL,

ArchivedOn datetime default getdate())

go

insert EventlogHistory

select * from Eventlog

String or binary data would be truncated.

Never use Select * from ... . It's a way to confusion.

P.S. Oh, i'm sleeping too it was well elaborated already.

halifaxdal SSCoach Points: 19741 More actions · Answer 3

mister.magoo (1/16/2015)
halifaxdal (1/16/2015)
Hello, I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take, if you have a better recommendation, it would be appreciated if you can share it with me (and others reading this post):
If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
127.0.0.1 [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
Problem #1 : The identity column means you can't be inserting without specifying the columns.
Problem #2 : That table is not an exact replica of the source table, it has at least one extra column, so you can't be inserting into it from a SELECT *.
Problem #3 : Don't ever use insert without a column list, and don't use select *
Problem #4 : If you are using an IDENTITY column it's because you want to have a new set of numbers in this archive table for LogID, or else you would just have it as an int, wouldn't you?
It seems like you might be trying to obfuscate the real problem (as we have all had to do from time to time) and in the process you may be hiding the real problem, because there is no way that an insert into that table without a column list from a select star would even get compiled, let alone run and produce that error.
Of course, it is late and I could be wrong 😎

Mucha gracia to you and everyone else participating the discussion.