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

trimming empty spaces in large Table Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 2:33 PM
Points: 25, Visits: 339
How to trim a large table with more than 300 columns dynamically. when i tried with this code i was getting an error because the variable @sql is of nvarchar(max) and i have like 300 columns so all the column names were not coming when i run this query can any of the guys help me in this ?

DECLARE @SQL nVARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'MYTAbleName'

SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL`enter code here`
PRINT @SQL
EXECUTE @SQL
Post #1497988
Posted Tuesday, September 24, 2013 12:07 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
NOTE: Cross-posted at Stackoverflow: http://stackoverflow.com/questions/18988246/trimming-empty-spaces-in-large-table-sql-serever

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1498002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse