Hi All,
I am trying to generate MYSQL query by using this https://www.sqlservercentral.com/forums/topic/dynamically-select-load-column-names-into-select-statement , But not getting correct result.
Can you help.
CREATE TABLE tbl1
(col1 int,col2 int,col3 int,col4 int,col5 int)
go
CREATE TABLE t_column_details
(
[TABLE_SCHEMA] [varchar](500) NULL,
[TABLE_NAME] [varchar](500) NULL,
[COLUMN_NAME] [varchar](500) NULL,
[length] [bigint] NULL
)
go
insert into t_column_details values ('dbo','tbl1','col1',2)
insert into t_column_details values ('dbo','tbl1','col2',20)
insert into t_column_details values ('dbo','tbl1','col3',12)
insert into t_column_details values ('dbo','tbl1','col4',211)
insert into t_column_details values ('dbo','tbl1','col5',112)
go
select * from t_column_details
select * from sysobjects where name ='tbl1'
select 'UPDATE '+name+' JOIN tmp USING (mssql_id) SET '+STRING_AGG(table_name +'.'+ COLUMN_NAME,'=tmp.,')+';'
from [DBAUtil_New].dbo.t_column_details C left join sysobjects S on C.TABLE_NAME=S.name
where type='u'
and table_name ='tbl1'
group by name
--coming like this
UPDATE tbl1 JOIN tmp USING (mssql_id) SET tbl1.col1=tmp.,tbl1.col2=tmp.,tbl1.col3=tmp.,tbl1.col4=tmp.,tbl1.col5;
-- needed result
UPDATE tbl1 JOIN tmp USING (mssql_id) SET tbl1.col1 = tmp.col1, tbl1.col2 = tmp.col2, tbl1.col3 = tmp.col3,tbl1.col4 = tmp.col4,tbl1.col5 = tmp.col5;
December 26, 2022 at 8:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Moved the concatenation of tmp columnname in the STRING_AGG
select 'UPDATE '+name+' JOIN tmp USING (mssql_id) SET '+STRING_AGG(table_name +'.' + COLUMN_NAME+'=tmp.' + COLUMN_NAME,',')+';'
from dbo.t_column_details C left join sysobjects S on C.TABLE_NAME=S.name
where type='u'
and table_name ='tbl1'
group by name
December 27, 2022 at 2:39 pm
Thank you. Working fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply