动态更新语句 [英] Dynamic Update statement

问题描述

嗨朋友，



这里我在我的商店程序中使用光标，我打算在我的SP中取出光标，请给出一些解决方案避免光标语句与动态的正常更新语句。



示例如下：



更新表名集columnname（variable）=表A中的值连接A.condition = B.Condition上的表B.其中name ='Test'（变量）和age = 18（变量）



更新表名设置列名（变量）=表A中的值连接A.condition = B.Condition上的表B.其中name ='kumar'（变量）和age = 19（变量）



3更新表名设置列名（变量）=表A中的值连接A.condition = B.Condition上的表B.其中name ='babu'（变量）和age = 30（变量）



这是我的光标将如何工作300组合动态从表中选择数据并更新到主表



我是 尝试取出光标，更新语句应该像这样工作，而不是编写300更新语句我想写一个更新，其中所有300组合应该执行。



以下代码我必须带另一个解决方案。

Hi Friends,

Here i am using the cursor in my store procedure, i am planning to take out my cursor in my SP , please give some solution how to avoid the cursor statement to normal update statement with dynamic .

Example Below:

Update Tablename set columnname(variable) = value from table A join Table B on A.condition = B.Condition where name = 'Test'(variable) and age = 18(variable)

Update Tablename set columnname(variable) = value from table A join Table B on A.condition = B.Condition where name = 'kumar'(variable) and age = 19(variable)

3 Update Tablename set columnname(variable) = value from table A join Table B on A.condition = B.Condition where name = 'babu'(variable) and age = 30(variable)

This is how my cursor will work 300 Combination dynamically pick the data from table and update into the main table

I am trying to take out the cursor , and update statement should work similar like this , instead of writing 300 update statement i want to write one update where all the 300 combination should execute.

below the code i have to bring another solution.

BEGIN DECLARE @Type VARCHAR(100)
DECLARE @TargetColumn VARCHAR(100)
DECLARE @SourceColumn VARCHAR(100)
DECLARE @SQL varchar(max)

DECLARE a_cursor CURSOR STATIC
FOR
SELECT [Type],[SourceColumn],[TargetColumn] FROM ref.tblEdsMap
GROUP BY [Type],[SourceColumn],[TargetColumn]
OPEN a_cursor
FETCH NEXT FROM a_cursor INTO @Type,@SourceColumn,@TargetColumn
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'UPDATE GCT SET GCT.' + @TargetColumn + ' = map.[TargetValue]
from EdsMap map
JOIN Table GCT
ON GCT.' + @SourceColumn + ' = map.[SourceValue]
where map.[Type]=''' + @Type + ''' and map.SourceColumn=''' + @SourceColumn+ ''''
Exec (@SQL)
PRINT @SQL
FETCH NEXT FROM a_cursor INTO @Type,@SourceColumn,@TargetColumn
END
CLOSE a_cursor
DEALLOCATE a_cursor
END

推荐答案

hi，





看看它是否适合你...

see if it works fine for you...

BEGIN DECLARE @Type VARCHAR(100)
DECLARE @TargetColumn VARCHAR(100)
DECLARE @SourceColumn VARCHAR(100)
DECLARE @SQL varchar(max)
Declare @tblcount int
Declare @count int
 

SELECT ROW_NUMBER() OVER (ORDER BY [Type]) AS Row,[Type],[SourceColumn],[TargetColumn] into #temp 
FROM ref.tblEdsMap
GROUP BY [Type],[SourceColumn],[TargetColumn]


set @tblcount=(select COUNT(*) from #temp)
set @count=0

while(@count<@tblcount)
begin 

	select @Type=[Type],@SourceColumn=[SourceColumn],@TargetColumn=[TargetColumn] from #temp 
	where Row=@count;
	 
	SET @SQL = 'UPDATE GCT SET GCT.' + @TargetColumn + ' = map.[TargetValue]
	from EdsMap map
	JOIN Table GCT
	ON GCT.' + @SourceColumn + ' = map.[SourceValue]
	where map.[Type]=''' + @Type + ''' and map.SourceColumn=''' + @SourceColumn+ ''''
	Exec (@SQL)

	set @count=@count+1;
end

drop table #temp

这篇关于动态更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！