I have been working on a database for some days and was tasked with cleaning out records that were extremely old and possessed data that was not valid on our application anymore. The database being worked on is a relational database and the data model possesses a lot of relationships. The whole model was generated code-first using the Entity Framework. This means that there were a few important details that were not efficiently defined whilst creating each table in the database. The most important one is the cascading options when deleting a reference to another table.
In order to overcome this problem without having to add a model migration for just these entries in particular, I decided to use write a script that could overcome problems with foreign keys. There was also the important requirement, that not all data should be deleted. So, the script was the best option and allowed me to be very specific as to what data I was deleting or updating.
Here are some useful tricks I learned whilst developing my script:
Variables
I am used to writing code in programming languages and even tried some scripting languages, but I had no idea that Transact Structured Query Language also allowed variables to be declared. The syntax is as straight-forward as most SQL keywords and statements.
DECLARE @VariableName <datatype>;
If you wish to declare a variable you simply use the keyword DECLARE. The variable needs to have an @ at the beginning of its name and you can define the datatype right after its name.
A variable does not even have to be limited to primitive datatypes. You can define a temporary table as a variable!
DECLARE @VariableName TABLE ( column1 <datatype>, column2 <datatype> );
To set the value of a variable, you have two options:
- SET keyword: SET @Variable = <value>;
- SELECT keyword: SELECT @Variable = <value>;
The main difference here is that the SET keyword only sets the value for one variable, the SELECT keyword can set multiple variables’ values by separating the declaration with a comma.
SELECT @Variable1 = <value>, @Variable2 = <value>;
In addition, you can integrate the variable declaration with a query.
SELECT @IDHolder = MyTable.Id, @NameHoder = MyTable.Name FROM MyTable;
INSERT INTO + SELECT + DECLARE to create a list
During my time writing my script, I had to get a list of IDs to iterate from. To this effect I used the INSERT INTO keywords and chained it with a SELECT. The table I was inserting into was a temporary table saved onto a variable.
DECLARE @MyList TABLE ( Id int IDENTITY, RequiredId int );
INSERT INTO
@MyList (RequiredId)
SELECT
MyTable.Id
FROM
MyTable
WHERE <predicate>;
The IDENTITY allows me to define a column in a table as a unique and auto incremental value. Very useful to act as the indexes in my list and iterate through each of them.
Iterating through the list
There is no for-loop in T-SQL but there is a WHILE loop. By counting the entries in my temporary table (currently acting as a list), I can set the length of it onto a variable and then use it to iterate through each record.
DECLARE @Length int;
SELECT @Length = COUNT(*) FROM @MyList;
DECLARE @CurrentIndex int;
SET @CurrentIndex = 1;
WHILE (@CurrentIndex <= @Length)
BEGIN
DECLARE @CurrentValue int;
SELECT @CurrentValue = @MyList.RequiredId FROM @MyList WHERE @MyList.Id = @CurrentIndex;
END
Duarte Ramos 