Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

String or binary data would be truncated. Expand / Collapse
Author
Message
Posted Friday, May 16, 2014 11:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:58 AM
Points: 61, Visits: 403
Howdy Everbody,

You can say this topic is easy, but... maybe not...

Table Temporary (Staging) - 400.000 rows and 200 columns.

When i done insert from temporary table to target, i have this:

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

How can i check which row/column truncated?
Post #1571832
Posted Friday, May 16, 2014 12:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 3,374, Visits: 7,303
You need DDL for your target table. And use some code like this:
SELECT *
FROM Temp_Table
WHERE LEN( string_column1) > length of string_column1 in target table
OR LEN( string_column2) > length of string_column2 in target table
...
OR LEN( string_columnN) > length of string_columnN in target table

With 400,000 rows, it shouldn't be too slow.

Ideally, your temp_table should have the same structure of your target table.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1571839
Posted Friday, May 16, 2014 12:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:58 AM
Points: 61, Visits: 403
SSCrazy,

Temporary table i mean - table physical.. is not #table..

my temporary table have 255 nvarchar for all columns (ETL)...

and my target table is thin (column) i mean, first column nvarchar(16), second column bit and... your script not right for me...
Post #1571852
Posted Friday, May 16, 2014 1:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:54 AM
Points: 29, Visits: 178
This error you are getting because your target table has column length different than the temp table.

Make sure you have target table structure same as temp table.

I used to get this error when I was loading files in SSIS task after doing research on this I found out
this is the problem of column length.


Thanks



Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding
Post #1571878
Posted Friday, May 16, 2014 1:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:54 AM
Points: 29, Visits: 178
Here is the script to copy table structure from temp to target.

CREATE TABLE TagetTableName
AS
SELECT TOP 0 * FROM TempTableName.


Thanks



Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding
Post #1571881
Posted Friday, May 16, 2014 1:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 3,374, Visits: 7,303
I never spoke about a real temp table (#table), I just followed the description that you used.
I must not have explained myself clearly. I made an example to show you what I meant.
Be sure to read the comments to follow the test.
--Prepare the scenario
CREATE TABLE Staging(
Column1 nvarchar(255),
Column2 nvarchar(255))
INSERT INTO Staging
VALUES('Correct','0'),
('Very Long String that won''t fit in the column','1'),
('Wrong bit', '235')
CREATE TABLE TargetTable(
Column1 nvarchar(16),
Column2 bit)
GO
--This will fail
INSERT INTO TargetTable
SELECT *
FROM Staging
GO
--No columns were added to TargetTable
SELECT *
FROM TargetTable
GO
--We identify rows that are giving errors
SELECT *
FROM Staging
WHERE LEN( Column1) > 16
GO
--We insert into TargetTable the rows with no errors
INSERT INTO TargetTable
SELECT *
FROM Staging
WHERE LEN( Column1) <= 16
GO
--We have 2 rows because any value different than 0 or null will be converted to 1 when converting to bit
SELECT *
FROM TargetTable
GO
--Clean TargetTable
TRUNCATE TABLE TargetTable
--Explicitly define the truncation to avoid errors
INSERT INTO TargetTable
SELECT LEFT(Column1, 16),
Column2
FROM Staging
--All 3 rows on the TargetTable
SELECT *
FROM TargetTable
GO
--Clean everything
DROP TABLE TargetTable
DROP TABLE Staging




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1571882
Posted Friday, May 16, 2014 1:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 3,374, Visits: 7,303
viresh29 (5/16/2014)
Here is the script to copy table structure from temp to target.

CREATE TABLE TagetTableName
AS
SELECT TOP 0 * FROM TempTableName.

That's a bad design solution as no one should have a table with just nvarchar(255) columns.

If using SSIS, I would use derived columns to correct the length to fit the target table and create an error handler to identify all rows that would cause an error and correct the problem.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1571884
Posted Friday, May 16, 2014 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:54 AM
Points: 29, Visits: 178
Actually in my case I had Data Dictionary. So I used to get all data types from there. so

Cause At my work We were loading first client data to template tables and I used to use CTAS to copy tables and loading those template tables data to client tables and after finishing process truncate template tables.


Thanks



Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding
Post #1571890
Posted Friday, May 16, 2014 1:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:07 PM
Points: 21,351, Visits: 15,032
As Louis said, do a select len(whateveryourcolumnis) for all of the columns and find which one has lengths that are beyond your target table definitions.

Having a staging table with data types that don't match the target table can work, but you have to know that you have to explicitly truncate the data when moving from the temp (staging) table to the target table or you will get errors. Otherwise, I prefer to match the design of my staging tables with that of the target tables.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1571893
Posted Saturday, May 17, 2014 10:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:58 AM
Points: 61, Visits: 403
I want to make it clear that I not Expert in SSIS, but my logic is it:

1. truncate table [tblstaging]

2. load from csv to [tblstaging]
obs 1: I have no idea which is length of column... so I created - nvarchar(255). I confess that i never used a Derived column and I don't know if it can help me.

obs 2: I prefer use nvarchar(255) to do not check the real size and others, can be slow if I have exact value in process ETL, constraint and more..

I said that because my client release each 2 hour one csv and I have no idea if always is the same columns length so I need always treat this process.

3. After load in temporary table, i do several updates and more... after this, insert select...

but the big problem is insert select...
Post #1572011
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse