0

I'm working on SQL Server 2008.

I delete all data from a table and then I try to insert value to the table. Here's the code:

TRUNCATE TABLE [dbo].[STRAT_tmp_StratMain]

INSERT INTO [dbo].[STRAT_tmp_StratMain] ([FileNum])
    SELECT [dbo].[STRAT_tmp_Customer].[NumericFileNumber]
    FROM [dbo].[STRAT_tmp_Customer];

The FileNum in STRAT_tmp_StratMain is float number and is also index and can't be null.

NumericFileNumber is float and can be null but is never null and there are no duplicates in it (each row is unique number).

The table STRAT_tmp_StratMain contain much more fields but all can be null and also has a defualt values.

When I try to run this query I get the error:

Msg 8152, Level 16, State 4, Line 1 String or binary data would be truncated. The statement has been terminated.

I tried also to do simply:

INSERT INTO [dbo].[STRAT_tmp_StratMain] ([FileNum]) Values (1);

Still get the same error.

Any ideas?

Thanks, Ilan

user2743760
  • 201
  • 2
  • 9
  • could you add the actual ddl of `[STRAT_tmp_StratMain]` to the question (with defaults and triggers)? – A ツ Aug 12 '15 at 20:52
  • It is hard to say without seeing the table definition, but it looks like you are trying to insert data into a column that will end up truncating the data – nLee Aug 12 '15 at 20:52
  • possible duplicate of [SQL Server Error : String or binary data would be truncated](http://stackoverflow.com/questions/10843567/sql-server-error-string-or-binary-data-would-be-truncated) – nLee Aug 12 '15 at 20:56
  • Hmm, see the edit to my answer/comment below. – Tab Alleman Aug 12 '15 at 20:58
  • You would be far better off using an exact numeric instead of an approximate like float. I suspect that since this your FileNum it is NOT going to be ok if you can't get the exact value back. – Sean Lange Aug 12 '15 at 21:03

2 Answers2

1

I am not able to reproduce your issue. When I run this code on SQL Server 2008, I get no error:

DECLARE @tt TABLE (FileNum float NOT NULL);

INSERT INTO @tt (FileNum) VALUES (1);

Check the Default constraints on all the columns in your target table and make sure none of them would try to insert a string value that would truncated by the datatype limitations of the column.

example: SomeColumn varchar(1) DEFAULT 'Hello'

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thank you so much!!! I add you a point, for two reasons: 1. You realy read fully my question and understand it. 2. You were right, it was one of the other fields that was nvchar(3) and the defualt was '@@@@'. – user2743760 Aug 13 '15 at 15:52
  • Glad it helped, I have edited the answer and taken out the conjecture and just left in the part that actually fixed the problem, so now it is in fact an answer instead of a comment that leads to an answer. – Tab Alleman Aug 13 '15 at 16:01
0

This due to the data you are trying to insert does not fit in the field: if you have a defined length of (say) 10 or 50 characters but the data you are trying to insert is longer than that.

Mathusuthanan
  • 117
  • 6