Two Questions To Help Me Improve SQL Scripts

[annethorne]

Hi SQL Developer Friends,

I would like implement the following techniques in some SQL scripts:

1) Before updating a table, copy it into a new table that has the same table name, appended with an underscore and the datetime. For example if the name of the table was Browsers then I would copy the contents of that table into a new table named Browsers_20120512_23:35 or something like that... Right now I am doing this... but I would prefer the backup table to have the datetime in the name:

SELECT * INTO _overriderules_bak FROM OverrideRules;

2) I would like to include in some scripts a loop so that different data could be inserted or updated using the same pattern pattern. For example, below, is a portion of the of script I am now writing... which involves a lot of cutting and pasting. I'd like to write a method once and then just have a loop. Notice that the only difference between the first code block and the second is that in the first the CountryID = 1, and in the second, the CountryID = 11. I have other scripts where more than one variable are different... but this should give you a general idea of what I'm looking for:

SET IDENTITY_INSERT OverrideRules ON 

declare @overrideId as int;

--Obtain max id from OverrideRules (there is no identity property)
SELECT @overrideId = max([Override_id]) + 1 FROM [OverrideRules]

--OverrideRules
IF EXISTS (SELECT * FROM OverrideRules WHERE Override_notes='RegistrationSKU' and CountryID = '1')
BEGIN
	UPDATE [OverrideRules]
	SET [Override_notes]='RegistrationSKU',[Category]='Registration',[CountryID]='1'
	WHERE  Override_notes='RegistrationSKU' and CountryID = '1'
END
ELSE
BEGIN
INSERT INTO [OverrideRules]
           ([Override_id],[Override_notes],[Category],[CountryID])
     VALUES(@overrideId,'RegistrationSKU','Registration','1')
set @overrideId = @overrideId + 1
END

IF EXISTS (SELECT * FROM OverrideRules WHERE Override_notes='RegistrationSKU' and CountryID = '11')
BEGIN
	UPDATE [OverrideRules]
	SET [Override_notes]='RegistrationSKU',[Category]='Registration',[CountryID]='11'
	WHERE  Override_notes='RegistrationSKU' and CountryID = '11'
END
ELSE
BEGIN
INSERT INTO [OverrideRules]
           ([Override_id],[Override_notes],[Category],[CountryID])
     VALUES(@overrideId,'RegistrationSKU','Registration','11')
set @overrideId = @overrideId + 1
END
...
...
...
SET IDENTITY_INSERT OverrideRules OFF

Thank you so much for any guidance that any one can give.

Anne

 

[Olaf Doschke]

First observation:

SET IDENTITY_INSERT OverrideRules ON 
...
 --Obtain max id from OverrideRules (there is no identity property)

This is contradiciting. If OverrideRules has no Identity Integer column you don't need to set IDENTITY_INSERT ON.
This is just needed, if you want to override the auto numbering and want to insert records with your predetermined ID instead.

Overall it seems you have a small configuration table here you modify or synch. Because backing up a whole table before modifying it is not what a database is meant for. You get a much more lightweight history of your data using audit trail on the record level. This can be done using TRIGGERS, especially you can use an INSTEAD OF UPDATE TRIGGER to get the record as it is before the insert, store it in a secondary table (but only one) and then do the Update of the old record. This way records start in your table and get backed up, once they are updated.

You can accompany that using an INSTEAD OF INSERT trigger to test for existance of a record with a a certain Override_notes and Country value to then rather update that, instead of inserting a new record. And you also automatically get the record history then, when updating instead of inserting.

So in this case for both aspects of your question, the backup of data and the function to use can be solved by triggers. And I don't think I make the mistake of recommending that same tool to you, as I only know that (as in "If all you have is a hammer every problem looks like a nail.") This is really the best thing to use for both functionality of auditing data changes and avoiding double records.

Let me think. You could skip the backup completely by relying on transaction data SQL Server writes. But it's not easy to revert a single table and you can only go back to a certain state in time, not just for a certain record. This also is the case with your backup strategy. It's really a nightmare, if your table grows large, audit trail of each record is also possible with very large tables and just has the overhead of storage place needeed for each version of each record, not each version of the full table. This is much less volume.

And finally, I won't keep functions from you, as that is what you asked for in regard of less copy&paste coding. In general this is a good approach, so take a look at it:
[URL unfurl="true"]http://msdn.microsoft.com/en-US/library/ms186755.aspx[/url]
[URL unfurl="true"]http://msdn.microsoft.com/en-US/library/ms187926.aspx[/url]
Look into the examples sections.

But also look into triggers:
[URL unfurl="true"]http://msdn.microsoft.com/en-US/library/ms189799.aspx[/url]

Createing both an instead of insert and an instead of update code, you will finally just write INSERTS in your code, the instead of insert will than turn these into updates, if a note for a country already exists, and that update will then backup the old record version.

Bye, Olaf.

 

[Olaf Doschke]

you will finally just write INSERTS in your code, the instead of insert will then turn these into updates, if a note for a country already exists, and that update will then backup the old record version.

At least that's my idea of it. The code you need in the insert is quite 1:1 what you already have. The instead of update trigger will need to INSERT inserted into _overriderules_bak and then do the update anyway.

It's beyond my time to write and test this for you. On the other hand, if you run your script just once, I see no need to overcomplicate this, you can stay with your way, it's not wrong, just a bit more verbose. Verbose code is not necessarily bad.

Introducing a function you only use once would not be a good idea. Introducing a trigger even more so, as it is bound to a table and influences any insert or update done, not only by your script. But that's also the charme of triggers, you implement them once and they work on any code manipulating table data. you don't miss a backup or merge of data this way.

Bye, Olaf.