-1

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?

Community
  • 1
  • 1
William YK
  • 835
  • 7
  • 23
  • Are you asking can we convert results of select into an update? – TheGameiswar Aug 23 '16 at 19:13
  • Basically. So that I can update the same row with the original data. I am wanting to save it off to a *.sql file to run later. So not update from a select, because it's the same row and same table. – William YK Aug 23 '16 at 19:14
  • Does this help? [Link](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) – Sumeet Gupta Aug 23 '16 at 19:51
  • 1
    just back it up and use merge perhaps – S3S Aug 23 '16 at 19:52

1 Answers1

0

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
Robert Sievers
  • 1,067
  • 8
  • 12