title: Cleaning data dynamically after looping through table and columns
body: <p>Before I insert the results into the staging table, it needs to be 
  cleaned of all white spaces and carriage returns. I can sadly not provide 
  sample data because it is private company data. All tables have different 
  columns of type NVARCHAR*</p>
  
  <pre><code>DECLARE @NAME NVARCHAR(4000) = ''
  DECLARE @SQL NVARCHAR(4000);
  DECLARE @CNAME NVARCHAR(4000) = ''
  DECLARE @COUNT INT
  
  --Storing all the database names with schema id = dbo
  SELECT [name]
  INTO #TEMP
  FROM LIST.sys.tables
  WHERE [schema_id] = SCHEMA_ID('dbo')
  
  SELECT TOP 1 @NAME = [name] FROM #TEMP
  
  --Looping through all the tables
  WHILE (SELECT COUNT(*) FROM #TEMP) &gt; 0
  BEGIN 
  
  SELECT @CNAME += '[' + A.[Name] + '],'
  FROM LIST.sys.columns A
  JOIN LIST.sys.tables B
  ON A.[object_id] = B.[object_id]
  WHERE B.[object_id] = OBJECT_ID(@NAME)
  AND B.[schema_id] = 1
  AND A.[name] &lt;&gt; 'Inserted_Date'
  
  SET @CNAME = LEFT(@CNAME, LEN(@CNAME) - 1)
  
  SET @SQL = '
  SELECT ' + @CNAME + '
  INTO #TEMP2
  FROM [LIST].[dbo].[' + @NAME + ']
  EXCEPT
  SELECT ' + @CNAME + '
  FROM [LIST].[STG].[' + @NAME + ']
  
  --Here the cleaning needs to happen(Clean all columns from each table 
  dynamically)
  
  INSERT INTO [LIST].[STG].[' + @NAME + '] (' + @CNAME + ', [Inserted_Date])
  SELECT ' + @CNAME + ', GETDATE()
  FROM #TEMP2
  '
  
  EXEC(@SQL)           
  SET @CNAME = ''   
  SELECT TOP 1 @NAME = [name] FROM #TEMP  
  DELETE FROM #TEMP WHERE @NAME = [name]
  END
  DROP TABLE #TEMP
  </code></pre>