-1

I am using the following to update values in a SQL Server table:

UPDATE TABLE1 
SET COLUMN_A = REPLACE(COLUMN_A, ',', '')

I need to do this with multiple tables.

I am currently doing it manually one column at a time.

I would attempt to do this in SSIS using a for each loop component but I do not have access to SSIS in my current environment.

I would appreciate some guidance as to how to do this using perhaps a loop?

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1943247
  • 3
  • 1
  • 3
  • 2
    You know can `UPDATE` multiple columns in a single statement, correct? For example `UPDATE Table1 SET Col1 = 1, Col2 = 2;` – Thom A Jan 20 '21 at 11:03
  • 2
    What does your question have to do with CTEs as well? There's no CTE in your question, nor any mention of it. Please only add tags that are actually related to your question. As you've also said you can't use a For Each loop Container, I've removed the tag too. – Thom A Jan 20 '21 at 11:03
  • I hope this link [https://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005](https://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005) will help you. – Abhijeet Khandagale Jan 20 '21 at 11:50
  • Thank you Larnu, thats really useful, might use a formula in excel to create that for the whole table. Regarding the CTE tag, I had ben googling the issue prior to posting in stack overflow and i came across CTE as a possible solution, , I'll take note of that for future posts. – user1943247 Jan 20 '21 at 12:03
  • Thanks Abhijeet Khandagale, i will investigate that link – user1943247 Jan 20 '21 at 12:04

1 Answers1

0

I think you could do it with Dyanmic SQL, for example, you could use

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

and add some conditions to find all your table names, column names.

then use dynamic sql as a loop to go tables one by one.

DROP TABLE IF EXISTS #temp

CREATE TABLE #temp ---identity column will be used to iterate
(
id INT IDENTITY,
TableName VARCHAR(20),
ColumnName VARCHAR(20)
)

INSERT INTO #temp
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
-- choose your own results with where conditions

DECLARE @SQL VARCHAR(MAX)
DECLARE @Count INT = 1
DECLARE @Table VARCHAR(20)
DECLARE @Column VARCHAR(20)

WHILE @COUNT <= (SELECT COUNT(*) FROM #temp) 
BEGIN
    SELECT @table = TABLENAME FROM #temp WHERE id = @Count
    SELECT @Column = COLUMNNAME FROM #temp WHERE id = @Count
    SELECT @sql = 'SELECT TOP 10 '+@column +' FROM '+ @table

    PRINT @SQL
SET @Count = @Count + 1

END
Jiacheng Gao
  • 365
  • 3
  • 9
  • I just used select statement for each loop, you should change to your query. if the print result looks good to you, make sure to change print to EXEC – Jiacheng Gao Jan 20 '21 at 21:32
  • Thanks Jiacheng, I substituted my query and it generated a print of all the UPDATE statements I needed which I ran in a seperate query window. I had changed the PRINT comand to EXEC as you suggested but it outputted an error 'Could not find stored procesdure 'UPDATE.....' My issue was solved with the PRINT output so thank you for your help with this, stay safe. – user1943247 Jan 22 '21 at 13:14
  • Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. – HABO Jan 23 '21 at 14:28