September 24, 2018 at 10:23 am
I have a query which generates a SQL which I then copy and paste in a query window and execute. I am wondering if I can use loop so I don't have to copy and paste and execute all sql queries. but I don't even know how to create a loop for this update statement. Can someone help?select 'update [DataLake].[Staging].[
set ['+c.name+'] = RTRIM(LTRIM(['+c.name+']))'
from sys.tables t
join sys.columns c on t.object_id = c.object_id
where t.name = 'StgInitialCompanydata'
September 24, 2018 at 10:46 am
NewBornDBA2017 - Monday, September 24, 2018 10:23 AMI have a query which generates a SQL which I then copy and paste in a query window and execute. I am wondering if I can use loop so I don't have to copy and paste and execute all sql queries. but I don't even know how to create a loop for this update statement. Can someone help?select 'update [DataLake].[Staging].[StgInitialCompanydata]
set ['+c.name+'] = RTRIM(LTRIM(['+c.name+']))'
from sys.tables t
join sys.columns c on t.object_id = c.object_id
where t.name = 'StgInitialCompanydata'
Not sure I understand.
Guessing here. You have a table, or multiple tables, that have leading or training spaces that you want to strip off?
For starters, this will generate code for all columns, not just the character based columns. I think you are going to need to add a where clause to filter the non-character based columns.
sp_execute_SQL is likely the procedure you need to look into. There are numerous examples of that on this site alone.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 24, 2018 at 11:47 am
Yes, multiple columns in multiple tables. We have SSIS packages which we use to get the data from flat files into multiple staging tables and all values in all columns have leading or trailing spaces. Job runs every day. And once I have the data in a staging table, I clean the data (remove trailing, leading spaces, convert data etc.)
This is what I came up withDECLARE @sqlCommand varchar(1000)
DECLARE @ColumnName varchar(75)
DECLARE @TableName varchar(75)
SELECT @ColumnName = name from sys.columns
SELECT @TableName = name from sys.tables
SET @sqlCommand = 'update [Staging].'+@TableName+' set ['+@ColumnName+'] = RTRIM(LTRIM(['+@ColumnName+']))'
EXEC (@sqlCommand)
September 24, 2018 at 12:00 pm
Michael L John - Monday, September 24, 2018 10:46 AMNot sure I understand.
Guessing here. You have a table, or multiple tables, that have leading or training spaces that you want to strip off?
Yes, multiple columns in multiple tables. We have SSIS packages which we use to get the data from flat files into multiple staging tables and all values in all columns have leading or trailing spaces. Job runs every day. And once I have the data in a staging table, I clean the data (remove trailing, leading spaces, convert data etc.)
Are all the columns character strings?
September 24, 2018 at 12:04 pm
Michael L John - Monday, September 24, 2018 10:46 AMNot sure I understand.
Guessing here. You have a table, or multiple tables, that have leading or training spaces that you want to strip off?
Yes, multiple columns in multiple tables. We have SSIS packages which we use to get the data from flat files into multiple staging tables and all values in all columns have leading or trailing spaces. Job runs every day. And once I have the data in a staging table, I clean the data (remove trailing, leading spaces, convert data etc.)
This is what I came up withDECLARE @sqlCommand varchar(1000)
DECLARE @ColumnName varchar(75)
DECLARE @TableName varchar(75)
SELECT @ColumnName = name from sys.columns
SELECT @TableName = name from sys.tables
SET @sqlCommand = 'update [Staging].'+@TableName+' set ['+@ColumnName+'] = RTRIM(LTRIM(['+@ColumnName+']))'
EXEC (@sqlCommand)
Also, is the schema DataLake as in your original post or Staging as in the post quoted above?
Sorry, misread the post a bit.
September 24, 2018 at 12:18 pm
This might work:DECLARE @cmd VARCHAR(MAX) = '';
SELECT @cmd += '<SQL Command with ; terminator>; '
from sys.tables t
join sys.columns c on t.object_id = c.object_id
where t.name = 'StgInitialCompanydata'
AND collation_name IS NOT NULL; -- Limit it to character columns
EXEC (@cmd);
There are some fancier ways of doing it with FOR XML concatenation, or more primitive ways of doing it with a cursor. Take your pick.
September 24, 2018 at 12:36 pm
Getting an error when I ran the query DECLARE @sql nvarchar(1000)
DECLARE @ColumnName nvarchar(75)
DECLARE @TableName nvarchar(75)
SELECT @ColumnName = name from sys.columns
SELECT @TableName = name from sys.tables where name = 'StgInitialCompanydata'
SET @sql = 'update [Staging].StgInitialCompanydata set ['+@ColumnName+'] = RTRIM(LTRIM(['+@ColumnName+']))'
EXEC @sql
The name 'update [Staging].StgInitialCompanydata set [total_parse_duration] = RTRIM(LTRIM([total_parse_duration]))' is not a valid identifier.
.
This link didn't help
September 24, 2018 at 1:03 pm
NewBornDBA2017 - Monday, September 24, 2018 10:23 AMI have a query which generates a SQL which I then copy and paste in a query window and execute. I am wondering if I can use loop so I don't have to copy and paste and execute all sql queries. but I don't even know how to create a loop for this update statement. Can someone help?select 'update [DataLake].[Staging].[StgInitialCompanydata]
set ['+c.name+'] = RTRIM(LTRIM(['+c.name+']))'
from sys.tables t
join sys.columns c on t.object_id = c.object_id
where t.name = 'StgInitialCompanydata'
This is a situation where it is good to use a cursor:DECLARE @Cursor cursor
DECLARE @sSql nvarchar(MAX)
SET @Cursor = cursor FOR
SELECT 'UPDATE ' + quotename(c.table_schema) + '.' + quotename(c.table_name)
+ ' set ' + c.COLUMN_NAME + ' = LTRIM(RTRIM(' + quotename(c.COLUMN_NAME) + ')) WHERE '
+ quotename(c.COLUMN_NAME) + ' LIKE ''% '' OR ' + quotename(c.COLUMN_NAME) + ' LIKE '' %'';'
FROM INFORMATION_SCHEMA.columns c
WHERE c.DATA_TYPE IN('nvarchar','varchar','nchar','char')
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME,c.COLUMN_NAME
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @sSql
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @sSql
--EXEC (@sSql)
FETCH NEXT FROM @Cursor INTO @sSql
END
CLOSE @Cursor
DEALLOCATE @Cursor
Just uncomment the EXEC and comment out the PRINT to get it to run.
You might need to edit the query a bit to get it to update only the tables you want to update.
September 24, 2018 at 1:14 pm
NewBornDBA2017 - Monday, September 24, 2018 12:36 PMGetting an error when I ran the queryDECLARE @sql nvarchar(1000)
DECLARE @ColumnName nvarchar(75)
DECLARE @TableName nvarchar(75)
SELECT @ColumnName = name from sys.columns
SELECT @TableName = name from sys.tables where name = 'StgInitialCompanydata'
SET @sql = 'update [Staging].StgInitialCompanydata set ['+@ColumnName+'] = RTRIM(LTRIM(['+@ColumnName+']))'
EXEC @sqlThe name 'update [Staging].StgInitialCompanydata set [total_parse_duration] = RTRIM(LTRIM([total_parse_duration]))' is not a valid identifier.
.This link didn't help
Here is the code I came up with, and it assumes all columns are text:
DECLARE @SQLCmdTemplate NVARCHAR(MAX) = N'
UPDATE ~SchemaName~.~TableName~ SET
~Columns~
;';
DECLARE @UpdateColumns NVARCHAR(MAX)
, @SchemaName NVARCHAR(128)
, @TableName NVARCHAR(128)
, @ObjectId INT
, @SQLCmd NVARCHAR(MAX);
DECLARE TablesCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
[SchemaName] = [sch].[name]
, [TableName] = [tab].[name]
, [ObjectId] = [tab].[object_id]
FROM
[sys].[schemas] AS [sch]
INNER JOIN [sys].[tables] AS [tab]
ON [tab].[schema_id] = [sch].[schema_id]
WHERE
[sch].[name] = N'Staging';
OPEN [TablesCur];
WHILE 1 = 1
BEGIN
FETCH NEXT FROM [TablesCur]
INTO @SchemaName, @TableName, @ObjectId;
IF @@FETCH_STATUS <> 0
BREAK;
SELECT @UpdateColumns = STUFF((SELECT N', ' + QUOTENAME([col].[name]) + N'= RTRIM(LTRIM(' + QUOTENAME([col].[name]) + N'))'
FROM [sys].[columns] AS [col]
WHERE [col].[object_id] = @ObjectId
ORDER BY [col].[column_id]
FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,'');
SET @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLCmdTemplate,N'~SchemaName~',QUOTENAME(@SchemaName)),N'~TableName~',QUOTENAME(@TableName)),N'~Columns~',@UpdateColumns);
--SELECT CAST(N'<![CDATA[' + @SQLCmd + N']]>' AS XML);
EXEC [sys].[sp_executesql] @stmt = @SQLCmd;
END
CLOSE [TablesCur];
DEALLOCATE [TablesCur];
September 24, 2018 at 1:21 pm
Lynn Pettis - Monday, September 24, 2018 1:14 PMNewBornDBA2017 - Monday, September 24, 2018 12:36 PMGetting an error when I ran the queryDECLARE @sql nvarchar(1000)
DECLARE @ColumnName nvarchar(75)
DECLARE @TableName nvarchar(75)
SELECT @ColumnName = name from sys.columns
SELECT @TableName = name from sys.tables where name = 'StgInitialCompanydata'
SET @sql = 'update [Staging].StgInitialCompanydata set ['+@ColumnName+'] = RTRIM(LTRIM(['+@ColumnName+']))'
EXEC @sqlThe name 'update [Staging].StgInitialCompanydata set [total_parse_duration] = RTRIM(LTRIM([total_parse_duration]))' is not a valid identifier.
.This link didn't help
Here is the code I came up with, and it assumes all columns are text:
DECLARE @SQLCmdTemplate NVARCHAR(MAX) = N'
UPDATE ~SchemaName~.~TableName~ SET
~Columns~
;';DECLARE @UpdateColumns NVARCHAR(MAX)
, @SchemaName NVARCHAR(128)
, @TableName NVARCHAR(128)
, @ObjectId INT
, @SQLCmd NVARCHAR(MAX);DECLARE TablesCur CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
[SchemaName] = [sch].[name]
, [TableName] = [tab].[name]
, [ObjectId] = [tab].[object_id]
FROM
[sys].[schemas] AS [sch]
INNER JOIN [sys].[tables] AS [tab]
ON [tab].[schema_id] = [sch].[schema_id]
WHERE
[sch].[name] = N'Staging';OPEN [TablesCur];
WHILE 1 = 1
BEGIN
FETCH NEXT FROM [TablesCur]
INTO @SchemaName, @TableName, @ObjectId;IF @@FETCH_STATUS <> 0
BREAK;SELECT @UpdateColumns = STUFF((SELECT N', ' + QUOTENAME([col].[name]) + N'= RTRIM(LTRIM(' + QUOTENAME([col].[name]) + N'))'
FROM [sys].[columns] AS [col]
WHERE [col].[object_id] = @ObjectId
ORDER BY [col].[column_id]
FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,'');SET @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLCmdTemplate,N'~SchemaName~',QUOTENAME(@SchemaName)),N'~TableName~',QUOTENAME(@TableName)),N'~Columns~',@UpdateColumns);
--SELECT CAST(N'<![CDATA[' + @SQLCmd + N']]>' AS XML);
EXEC [sys].[sp_executesql] @stmt = @SQLCmd;
ENDCLOSE [TablesCur];
DEALLOCATE [TablesCur];
It worked. Thank you so much.
September 24, 2018 at 2:10 pm
Michael L John - Monday, September 24, 2018 10:46 AMNot sure I understand.
Guessing here. You have a table, or multiple tables, that have leading or training spaces that you want to strip off?
Yes, multiple columns in multiple tables. We have SSIS packages which we use to get the data from flat files into multiple staging tables and all values in all columns have leading or trailing spaces. Job runs every day. And once I have the data in a staging table, I clean the data (remove trailing, leading spaces, convert data etc.)
You have been given a T-SQL solution - but I have to wonder at all of the extra effort applied here to create code to trim spaces when it can be done simply enough in SSIS. In SSIS you would add a derived column transformation - and for each string field you would replace the field with TRIM([field name]). Depending on the what is being converted - and how you are converting the data - some of those (if not most) could be moved to SSIS also...and that can be done in either a derived column transformation or a data conversion transformation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 24, 2018 at 2:34 pm
Do you consider it extra effort to type LTRIM(RTRIM()) instead of TRIM()?
Unless you're generating the SSIS package automatically with BIML, you're doing a lot more work manually defining transformations for every character column than a metadata-driven SQL query. The UPDATE running on a server with multiple cores will probably run faster than a single-threaded SSIS package data flow. The query can easily be re-purposed for other tables, while the SSIS package would require extensive editing and recreation of all transformations.
September 24, 2018 at 3:31 pm
Scott Coleman - Monday, September 24, 2018 2:34 PMDo you consider it extra effort to type LTRIM(RTRIM()) instead of TRIM()?Unless you're generating the SSIS package automatically with BIML, you're doing a lot more work manually defining transformations for every character column than a metadata-driven SQL query. The UPDATE running on a server with multiple cores will probably run faster than a single-threaded SSIS package data flow. The query can easily be re-purposed for other tables, while the SSIS package would require extensive editing and recreation of all transformations.
TRIM is not available in SQL Server 2014.
September 24, 2018 at 3:41 pm
Jonathan AC Roberts - Monday, September 24, 2018 3:31 PMScott Coleman - Monday, September 24, 2018 2:34 PMDo you consider it extra effort to type LTRIM(RTRIM()) instead of TRIM()?Unless you're generating the SSIS package automatically with BIML, you're doing a lot more work manually defining transformations for every character column than a metadata-driven SQL query. The UPDATE running on a server with multiple cores will probably run faster than a single-threaded SSIS package data flow. The query can easily be re-purposed for other tables, while the SSIS package would require extensive editing and recreation of all transformations.
TRIM is not available in SQL Server 2014.
Not in T-SQL, but it IS available as part of an expression in SSIS. And I'm quite sure that's what Scott meant.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply