How to fix data truncation issue for columns in table variable with Datatype as varchar(max)

Question

I have a table variable with a column of type varchar(max). I am storing resultset of another query in this table variable. The length of the result that I get on querying this table is 7870. I wanted this result to be transposed using comma separator and before each value, I had to append "t." But this resulted in data exceeding 8000 so anything after that is getting truncated even if the datatype of the column is varchar(max)

Since datatype is varchar(max) so I don't want data to be truncated so tried setting property Set ANSI_WARNINGS OFF before inserting data to table variable But still no success.

DECLARE @FormattedMainResult VARCHAR(MAX)

DECLARE @CommonColumns TABLE (FinalColumns VARCHAR(MAX)) 

INSERT INTO @CommonColumns
    SELECT main.AllColumnsMain AS CommonColumns
    FROM [dbo].[Staging] Stg
    INNER JOIN [dbo].[Target] main ON Stg.AllColumnsStg = main.AllColumnsMain

SELECT SUM(LEN(FinalColumns)) AS MaxLen_FinalCommonColumns  
FROM @CommonColumns  -- 7870

SELECT * 
FROM @CommonColumns

Output:

Final Columns
---------------
John
Carl
Scott
Rick

SELECT @FormattedMainResult = (SELECT DISTINCT 
                                   STUFF((SELECT ',' + 't.' +u.FinalColumns
                                          FROM
                                              (SELECT FinalColumns 
                                               FROM @CommonColumns) u
                                          FOR XML PATH('')), 1, 1, '') AS userlist)  

SELECT SUM(LEN(@FormattedMainResult)) AS MaxLen_posttranspose  -- 8230 so data is truncated
PRINT @FormattedMainResult

Output

t.John,t.Carl,t.Sc

Actual result from @FormattedMainResult:

t.John,t.Carl,t.Sc (data truncated)

Expected result from @FormattedMainResult:

t.John,t.Carl,t.Scott,....,t.Rick (no truncation)

Update

Now I am facing issue while executing Insert into statement which I get from select query result.

Example,

Declare @Result nvarchar(max) 
DECLARE @FormattedStgResult varchar(MAX)
DECLARE  @joinCondition VARCHAR(MAX)='s.Id=t.Id'

SET @Result = ''
SET @Result = 'INSERT INTO ' + QUOTENAME('vjtest') +'.'+ QUOTENAME('dbo') +'.'+ QUOTENAME('Staging') +' (' + @FormattedStgResult + ')  
 SELECT ' + @FormattedMainResult + 
' FROM '+ QUOTENAME('vjtest') +'.'+ QUOTENAME('dbo')+'.'+QUOTENAME('Target') + ' t
 LEFT JOIN '+ QUOTENAME('vjtest') +'.'+QUOTENAME('dbo')+'.'+QUOTENAME('Staging')+ ' s
 ON ' + @joinCondition + 
' WHERE s.UniqueId IS NULL'

Exec(@Result)

@FormattedMainResult is same as old code which will have values with prefix "t." -> t.Id,t.Name,1,....,t.Rootpid

@FormattedStgResult has the same result as @FormattedMainResult only difference there is no prefix "t." -> Id,Name,IsDelete,...Rootpid

This does generate correct Insert statement with expected columns but when it is executed in Exec(@Result) it says 0 rows affected. However, when that same query I execute manually it does show 3 rows affected.

Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From. — HABO
– HABO, Commented Apr 14, 2019 at 15:28
To clarify: Using QuoteName( u.FinalColumns ) avoids surprises when getting column names from a possibly untrustworthy source, or just creatively named columns. Applying QuoteName() to 'dbo' doesn't offer much benefit. — HABO
– HABO, Commented Apr 15, 2019 at 14:01

Dan Guzman · Accepted Answer · 2019-04-14 10:43:05Z

3

The T-SQL comment in your code proves the actual variable length is 8230 characters. The issue is that PRINT is limited to 8000 characters and will truncate excess characters.

Use SELECT @FormattedMainResult; to return the entire value, unless truncated by SSMS according to max column length in results query option. Keep ANSI_WARNINGS ON unless you have legacy code that requires non-ISO SQL behavior.

edited Apr 14, 2019 at 10:43

answered Apr 14, 2019 at 10:34

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vikas J Over a year ago

Thanks, @Dan Guzman with SELECT my complete data is printed in the console. However, Result which I get from FormattedMainResult going forward in the process will be an Insert Into statement. So this Insert into Statment is printed in the console now perfectly but when I do EXEC(FormattedMainResult) it does not give an error but does not do the insert which it was supposed to.

Dan Guzman Over a year ago

@VikasJ, it's difficult to troubleshoot code we cannot see. Add the relevant code that builds the INSERT statement to your question.

Vikas J Over a year ago

I have added code in the update section of my question. For your reference.

Dan Guzman Over a year ago

@VikasJ, the added code is not assigning a value to @FormattedStgResult so @Result value with the SQL statement will be NULL and the execute will be a no-op.

Vikas J Over a year ago

Can you please help me here, from what I understand I am assigning the whole Insert into statement which includes other local variables results FormattedStgResult to Result variable and then execute the complete thing. The values in FormattedStgResult & FormattedMainResult are already assigned. before calling Insert Into statement.

|

Collectives™ on Stack Overflow

How to fix data truncation issue for columns in table variable with Datatype as varchar(max)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related