SQL Server Print UPDATE statement for a row based on select results?

ngynwnxp 于 12个月前发布在其他

关注(0)|答案(2)|浏览(77)

I have a scenario where there is Prod/Test and in the Test environment, I have a settings table (row) with many columns that I'd like to be able to run an UPDATE after a database refresh and set everything back.

This select would return a row of columns I want to preserve.

select * from mysettings a where a.company = 'abc'

Is there a way to dynamically take this result and output something like this:

UPDATE mysettings
    set field1 = 'some str',
        field2 = 2,
        field3 = 234234,
        field4 = '2016-08-23 18:51:42.000',
        ...
    where mysettings.company = 'abc'

The reason I say Dynamically is I have different companies and environments, and also some fields are strings , int , datetime , etc and the goal is to save this script off as a final "step" to perform.

EDIT: Similar to this method, except instead of INSERT I want UPDATE .

How to export all data from table to an insertable sql format?

sql-server

来源：https://stackoverflow.com/questions/39108998/print-update-statement-for-a-row-based-on-select-results

2条答案

按热度按时间

kadbb4591#

Would something like this work for you?

SELECT 'UPDATE mysettings 
SET field1 = ''' + mysettings.field1 + ''',
SET field2 = ''' + mysettings.field2 + ''',
...
WHERE mysettings.company = ''' + @company_name + ''''
FROM mysettings WHERE mysettings.compnay = @company_name

赞(0）回复(0）举报 12个月前

aemubtdh2#

Try this code it will work

--Declare SelectQuery variable
     declare @selectQuery varchar(max);
--Set Query 
        set selectQuery ='select  * from  mysettings where  where mysettings.company ='abc'+';
            UPDATE mysettings
                set field1 = 'some str',
                    field2 = 2,
                    field3 = 234234,
                    field4 = '2016-08-23 18:51:42.000',
                    ...
                where mysettings.company = 'abc'
        --Printing the Query
          print   @selectQuery

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Print UPDATE statement for a row based on select results?

2条答案

相关问题

热门标签

最新问答