CODEWITHSUNDEEP
sqlsql-servert-sql
InspiredBy
InspiredBy

Reputation: 4320

Update columns in multiple tables by names pulled from a temporary table

I have a temp table where various table names and connected column names are stored. If I were to run a simple SELECT on it the results would look something like this:

----------------
TableName |  ColumnName
------------------
Users     |  RoleId
Tables    |  OwnerId
Chairs    |  MakerId
etc...

I'm looking for a way to set mentioned column values in the connected tables to NULL.

I know how to do it via a CURSOR or a WHILE loop by processing each row individually but I'm trying to eliminate these performance hoarders from my stored procedures. Is there any way to build a join by table names from the TableName column to the actual tables to then set connected ColumnName column values to NULL ?

Upvotes: 0

Views: 103

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

Try this,

IF OBJECT_ID('SampleTable') IS NOT NULL
    DROP TABLE SampleTable

CREATE TABLE SampleTable
(
Table_Name VARCHAR(50) NOT NULL,
Column_Name VARCHAR(50) NOT NULL
)
GO

INSERT INTO SampleTable
VALUES
('Users','RoleId'),('Tables','OwnerId'),('Chairs','MakerId')
,('Users','Appid'),('Tables','Column') --Give your Combo here
GO


declare @Sql nvarchar(1000)=''
;with CTE as
(
select QUOTENAME(a.Table_Name)Table_Name
,stuff((select ','+QUOTENAME(Column_Name),'=null' 
from SampleTable B 
where a.Table_Name=b.Table_Name for xml path('') ),1,1,'')UpdateCol
from SampleTable A
group by a.Table_Name
)

select @Sql=coalesce(@Sql+char(13)+char(10)+SingleUpdate,SingleUpdate)
from
(
select CONCAT('Update ',Table_Name,' ','SET ',UpdateCol)SingleUpdate
from cte
)t4

print @Sql
select @Sql
Execute sp_executeSql @Sql

Upvotes: 1

Arulmouzhi
Arulmouzhi

Reputation: 2254

Check this Script-

IF OBJECT_ID('SampleTable') IS NOT NULL
    DROP TABLE SampleTable

CREATE TABLE SampleTable
(
Table_Name VARCHAR(50) NOT NULL,
Column_Name VARCHAR(50) NOT NULL
)
GO

INSERT INTO SampleTable
VALUES
('Users','RoleId'),('Tables','OwnerId'),('Chairs','MakerId') --Give your Combo here
GO

--Check this scripts
SELECT 'UPDATE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(S1.TABLE_NAME) +
       ' SET ' + QUOTENAME(S1.COLUMN_NAME) + ' = NULL ; '
AS [Dynamic_Scripts]
FROM SampleTable S JOIN INFORMATION_SCHEMA.COLUMNS S1 ON s.Table_Name=s1.Table_Name and s.Column_Name=s1.Column_Name

--Check this scripts (multiple column single script; 1 table 'n' column - 1 update query)
SELECT 'UPDATE ' + CONCAT('[',TABLE_SCHEMA,'].[',S1.TABLE_NAME,'] SET ') + STRING_AGG(CONCAT('[',S1.COLUMN_NAME,']=NULL'),',') + ' ; ' AS [Dynamic_Scripts]
FROM SampleTable S JOIN INFORMATION_SCHEMA.COLUMNS S1 ON s.Table_Name=s1.Table_Name and s.Column_Name=s1.Column_Name
GROUP BY CONCAT('[',TABLE_SCHEMA,'].[',S1.TABLE_NAME,'] SET ')

Upvotes: 1

Related Questions