-2

I'm working with some tables in an SQL-Server database, and I'm doing some crucial modifications. In order not to loose everything when I mess up, I decided to do a "Generate script" (data only, I'm working with Microsoft SQL Server Management Studio), so now I have a bunch of SQL "INSERT" commands, like:

INSERT [dbo].[User_Group_Access] ([Visible], [GroupId], [Enabled], [ControlId], [Administrator]) VALUES (0, 26, 0, 181, 0)
INSERT [dbo].[User_Group_Access] ([Visible], [GroupId], [Enabled], [ControlId], [Administrator]) VALUES (0, 26, 0, 182, 0)

Oops, I did something wrong.
No problem, I thought. Just replace INSERT by UPDATE and off we go, but this seems not that simple: looking on StackOverflow for this question there are entire discussions about transactions, different database technologies, ...

Ok, the UPDATE command does not work.
But there should be some other command (REPLACE doesn't work, SUBSTITUTE does not exist as a command), isn't it?

Does anybody have an idea?
Thanks in advance

2 Answers 2

1

You can't just find and replace INSERT with UPDATE and have it work, the command syntaxes are completely different.

If you want to get back to the EXACT state you were in before you started this, which is what it sounds like then the fastest path for you (I think) is the following. Please note that this will cause you to lose ANY changes (new records, modified records) that were made in the meantime.

  • Truncate the table
  • Run the generated script.

However, if you are instead of a need to do some sort of ... merge of the data, your choices become harder. In that case I would:

  • Create a new table with the same schema as the source.
  • Search and replace in your script to change the table it's trying to insert into.
  • Run your script (inserting into the NEW table)
  • Write whatever merge/update/correction script you need now that you have the OLD version in a table you can query against.

As a further aside, my preference for this is not to script the data, but to just make a copy of it. Example:

SELECT * INTO dbo.MyTableName_20240215 FROM dbo.MyTableName

would quickly create a new table with the date appended to it. I could then do my operations on the real table (or do it on the copy and check my work, whatever works best). I could then failback with whatever method best fit my needs.

1
  • In the meantime I've solved my problem: I've tried to truncate my tables (using DELETE ...) but then I was faced with relations between the tables, so I needed to rearrange the DELETE ... commands. Once I figured that out, there still seemed to be something missing, but instead of trying to solve this, I just restored a backup I had taken before, bringing me back to the most important lesson about databases: "If you're planning to do something which can mess up your database, DO NOT RELY ON THE GENERATE SCRIPT feature, BUT TAKE A COMPLETE BACKUP!" :-)
    – Dominique
    Feb 15 at 15:34
1

Take a database snapshot before you start. If you mess up restore from that snapshot, fix your script and repeat.

Restore from snapshot will be faster than taking a full database backup and restoring from that.

All work performed, by yourself and by others, will be lost when you restore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.