May 16, 2014 at 11:49 am
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?
May 16, 2014 at 12:00 pm
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.
May 16, 2014 at 12:26 pm
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...
May 16, 2014 at 1:03 pm
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
May 16, 2014 at 1:05 pm
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
May 16, 2014 at 1:05 pm
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
May 16, 2014 at 1:08 pm
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.
May 16, 2014 at 1:33 pm
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
May 16, 2014 at 1:49 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 17, 2014 at 10:57 am
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...
May 17, 2014 at 11:09 am
Grass,
What is CTAs?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply