Create hashcode or unique id for all columns in a table?

Transact-SQL
1

Hello,

Hope someone can help.

We're going to be doing some data appending to three tables in our production database and we need to have a foolproof way of making sure the existing data doesn't get touched (this is being done via the front end of the software that sits on top of the SQL database).

I could take copies of the three tables before, dump them out to Excel, create a unique id by concatenating all the columns together and then do the same with the tables after; I'd then use a VLOOKUP in Excel to validate the before data hadn't been touched.

But that's very long winded and cpu / memory intensive.

Is there a way of adding a column containing a hashcode based on all preceding columns to my "before" tables?

So my table might look like

UID Name Address    Postcode  Hashcode
001 Fred 10 High St TR12 5HJ  0B675VC

Thanks in advance.

2

If you want to ensure that the tables aren't updated or deleted, an INSTEAD OF trigger can be used:

CREATE TRIGGER NoUpdate_Table1 ON table1 INSTEAD OF UPDATE,DELETE AS RETURN;

You'd create similar triggers on each table, each trigger needs a unique name. That will still allow data to be inserted, which supports the actions you described.

1 Like
3

How does this hashcode prevent the system from updating the data? Is the intent to prevent the updates - or is the intent to insure you can recover if something happens?

If the latter - a simple backup prior to the operation should be sufficient. You could even restore that copy as a different database to perform comparisons after the append process has completed.

Not sure why you would be concerned with a process that INSERTS data would somehow affect existing data. INSERT will only add new rows to a table.

2 Likes
4

How will you be doing this update?
Why are you doing in production and not in sandbox first to make sure nothing is touched.

2 Likes
5

Yes it's to recover in case something happens.

The inserts are done via the software itself, it's a process built into the package so it isn't just a case of writing a load of SQL INSERT statements and doing it natively.

6

We will be doing it in dev first so the hashcode would first be used there and then again in live (even though if it works in dev there's an almost certain likelihood that it'll work in live).

However, working remotely, if the connection drops at the wrong moment anything could happen.

7

How will you be doing this update? running the script using SSMS on someone's local machine remotely connected to dev/prod server.

Might want to rethink that process. run it on the server itself if there is concern of something wrong happening.

8

All you need is a backup - restore the backup to another name and you can validate what data has been changed.

An INSERT by definition cannot change existing data. If the process being run by the application is an append process and will be inserting new data to the tables - it cannot, by definition, modify existing rows.

I am not clear on what the concern here is...is the concern that the application won't just insert new data but could update existing data? If that is a valid concern then you need to follow up with the application team - and take a backup prior to this action.

9

Technically you also need to verify that an INSERT trigger does not modify data because of the INSERT. Unlikely, but remotely possible.