0

I have a MSSQL database that has a 'settings' table with more than 500 rows. I want to generate a script of UPDATE commands including all the data. This will run against an existing database, and the data will replace what is currently in the table.

I have attempted to use the answer found here: How to export all data from table to an insertable sql format? but I get INSERT statements, which won't work.

I cannot drop and re-create the table, because it has foreign keys.

Disclaimer: I did not design this. I wouldn't put foreign keys on settings.

CarComp
  • 1,929
  • 1
  • 21
  • 47
  • FYI - Management studio doesn't offer this capability as far as I know. I have accomplished the task using dbForge Studio Trial, but its paid software. It still stands that I'd like to know how to do it in the future without a paid program. – CarComp Mar 03 '20 at 15:12

1 Answers1

1

I use the Vroom SQL Script Generator for that. Its free and it does exactly what you are asking - generates insert/update scripts from a table. Just click on "Export Data", select the objects you want to export and then click the Generate button. If you need to really fine tune the way the script are generated then you can also use the built in automation scripting support - which is referenced at the bottom of the link.

enter image description here

NTDLS
  • 4,757
  • 4
  • 44
  • 70
  • That worked, but I was unable to choose the 'where' reference. I ended up having to copy the table, and reorder the rows to get the reference I needed. Thanks. – CarComp Mar 03 '20 at 15:42