I can hardly imagine making any complex database changes without using SQL scripts. We actually used them in this series so far, but we haven’t pointed out what they actually are. Today we’ll stand up against that injustice. Sit back, relax, and get ready to learn something new and useful.
Desired changes
Before we do anything else, we’ll describe what we have and what we want to achieve. This is not specific for databases only, but for almost any process – you should give yourself some time to think about the problem before you start coding/digging.
In the picture below, you can see our current data model. Tables country and city serve as storage where we’ll store data related to countries in cities. Unless our database serves only that purpose, this model is not too “smart”. I guess you want to have something more than just a list of countries and cities in your database:
This model served its’ purpose so far, but now we’re ready to move to the next level and we need something more complex. While we’ll need these two tables to store countries and cities, we’ll add a few more tables.
- Tip: You’ll often find some tables repeating in very different data models. These are the ones storing data that are used often and are being part of almost any business process. Some examples of such tables are the country, city, employee/user/account, customer/client, task, contact.
Now we have the data model with 6 tables. New tables are:
- employee – Contains a list of all employees in our company
- customer – Contains a list of all customers we’ve worked with
- call_outcome – Is a list of all possible ways any call could finish. Tables like this one are usually called dictionaries (catalogs). They are specific because values stored here almost never change and business logic is often directly related to these values. For example, call outcome could be “Call successful – sales” or “Customer rejected our offer”
- call – Contains details about calls employees had with customers. Please notice that in this table employees, customers and call outcomes are related
These 4 tables are much more than just a place to store the data in the way we did that with counties and cities. While everything in databases is closely related to our business, these 4 new tables are much more specific than the 2 ones we already had. Basically, they give the purpose to the whole data model.
Now we know where we are and where we want to be. The only thing we miss is how to get there. To get there we’ll use the SQL script. But before we do that, let’s see what the SQL script really is.
SQL Script
In programming, scripts are the series of commands (sequence of instructions) or a program that will be executed in another program rather than by the computer processor (compiled programs are executed by computer processor –> please notice that the script is not compiled).
Same stands for SQL scripts. The only thing that is specific is that commands in such scripts are SQL commands. And these commands could be any combination of DDL (Data Definition Language) or DML (Data Manipulation Language) commands. Therefore, you could change the database structure (CREATE, ALTER, DROP objects) and/or change the data (perform INSERT/UPDATE/DELETE commands).
It’s desired that you use scripts, especially when you’re deploying a new version and you want to keep current data as they were before that change.
Backup and restore
Using scripts is usually related to making significant changes in the database. I might be paranoid about this, but I prefer to backup the database before these changes.
-
Tip: If you expect major changes in your databases, either in structure, either data changes, creating a backup is always a good idea. You could backup the entire database or only 1 table. That’s completely up to you and the changes you’re making.
Oldie but goodie: “There are two types of people: 1. People who do backup & 2. People who will start doing backup.”
In SQL Server you can easily backup the whole database. First right-click on the database you want to backup, go to Tasks, and choose the Back Up option:
After choosing the backup option, you’ll get the pop-up as on the picture below:
Click OK, and your backup is created. Now we can create a copy of our database. We’ll do that by restoring from the backup. To do that, follow the process in the picture below:
You’ll need to enter the name of the restored database (our_first_database_old) and click OK:
The result of these actions shall be that now we have 2 identical databases – our_first_database & our_first_database_old. We’ll use the first one as the one where we’ll run our SQL script. In case we would screw something up, we could always easily revert changes using our backup.
SQL Script – example
Now we’re ready to take a look at our script. We want to do two different things:
- Create new database objects (tables and relations) – DDL commands, and
- Populate these tables with data – DML commands
Of course, we’ll run DDL commands first and then run DML commands. Trying to insert data into a table that doesn’t exist would result in errors. So, let’s take a look at our script now:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
-- tables -- Table: call CREATE TABLE call ( id int NOT NULL IDENTITY(1, 1), employee_id int NOT NULL, customer_id int NOT NULL, start_time datetime NOT NULL, end_time datetime NULL, call_outcome_id int NULL, CONSTRAINT call_ak_1 UNIQUE (employee_id, start_time), CONSTRAINT call_pk PRIMARY KEY (id) ); -- Table: call_outcome CREATE TABLE call_outcome ( id int NOT NULL IDENTITY(1, 1), outcome_text char(128) NOT NULL, CONSTRAINT call_outcome_ak_1 UNIQUE (outcome_text), CONSTRAINT call_outcome_pk PRIMARY KEY (id) ); -- Table: customer CREATE TABLE customer ( id int NOT NULL IDENTITY(1, 1), customer_name varchar(255) NOT NULL, city_id int NOT NULL, customer_address varchar(255) NOT NULL, next_call_date date NULL, ts_inserted datetime NOT NULL, CONSTRAINT customer_pk PRIMARY KEY (id) ); -- Table: employee CREATE TABLE employee ( id int NOT NULL IDENTITY(1, 1), first_name varchar(255) NOT NULL, last_name varchar(255) NOT NULL, CONSTRAINT employee_pk PRIMARY KEY (id) ); -- foreign keys -- Reference: call_call_outcome (table: call) ALTER TABLE call ADD CONSTRAINT call_call_outcome FOREIGN KEY (call_outcome_id) REFERENCES call_outcome (id); -- Reference: call_customer (table: call) ALTER TABLE call ADD CONSTRAINT call_customer FOREIGN KEY (customer_id) REFERENCES customer (id); -- Reference: call_employee (table: call) ALTER TABLE call ADD CONSTRAINT call_employee FOREIGN KEY (employee_id) REFERENCES employee (id); -- Reference: customer_city (table: customer) ALTER TABLE customer ADD CONSTRAINT customer_city FOREIGN KEY (city_id) REFERENCES city (id); -- insert values INSERT INTO call_outcome (outcome_text) VALUES ('call started'); INSERT INTO call_outcome (outcome_text) VALUES ('finished - successfully'); INSERT INTO call_outcome (outcome_text) VALUES ('finished - unsuccessfully'); INSERT INTO employee (first_name, last_name) VALUES ('Thomas (Neo)', 'Anderson'); INSERT INTO employee (first_name, last_name) VALUES ('Agent', 'Smith'); INSERT INTO customer (customer_name, city_id, customer_address, next_call_date, ts_inserted) VALUES ('Jewelry Store', 4, 'Long Street 120', '2020/1/21', '2020/1/9 14:1:20'); INSERT INTO customer (customer_name, city_id, customer_address, next_call_date, ts_inserted) VALUES ('Bakery', 1, 'Kurfürstendamm 25', '2020/2/21', '2020/1/9 17:52:15'); INSERT INTO customer (customer_name, city_id, customer_address, next_call_date, ts_inserted) VALUES ('Café', 1, 'Tauentzienstraße 44', '2020/1/21', '2020/1/10 8:2:49'); INSERT INTO customer (customer_name, city_id, customer_address, next_call_date, ts_inserted) VALUES ('Restaurant', 3, 'Ulica lipa 15', '2020/1/21', '2020/1/10 9:20:21'); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 4, '2020/1/11 9:0:15', '2020/1/11 9:12:22', 2); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 2, '2020/1/11 9:14:50', '2020/1/11 9:20:1', 2); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 3, '2020/1/11 9:2:20', '2020/1/11 9:18:5', 3); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 1, '2020/1/11 9:24:15', '2020/1/11 9:25:5', 3); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 3, '2020/1/11 9:26:23', '2020/1/11 9:33:45', 2); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 2, '2020/1/11 9:40:31', '2020/1/11 9:42:32', 2); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 4, '2020/1/11 9:41:17', '2020/1/11 9:45:21', 2); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (1, 1, '2020/1/11 9:42:32', '2020/1/11 9:46:53', 3); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 1, '2020/1/11 9:46:0', '2020/1/11 9:48:2', 2); INSERT INTO call (employee_id, customer_id, start_time, end_time, call_outcome_id) VALUES (2, 2, '2020/1/11 9:50:12', '2020/1/11 9:55:35', 2); |
Our script is a series of CREATE TABLE commands (creating 4 new tables), ALTER TABLE commands (adding foreign keys to these tables) and INSERT INTO commands (populating tables with the data). For INSERT commands, I’ve once more used Excel to create commands from the set of values:
Everything should go smoothly and you should see this under “Messages” (a lot of (1 row affected messages)) as well the message “Query executed successfully” (and everything shall be green). In case something wouldn’t be OK, you’ll notice that 🙂
On the other hand, in the database list, when you expand our_first_database tables, you should see all the new tables. Now, we’re sure that we have new objects (tables) in our database. But what about the data? We’ll check the contents of these tables with simple SELECT statements.
SQL Script – Comment
As you’ve seen, in our SQL script we’ve combined DDL and DML commands. That will usually be the case when you’re deploying the new version of the database on the system that is currently live. You can’t simply delete everything and create new objects (tables & relations) because you would lose existing data and nobody really wants that (losing the data is probably the worst thing you could do about databases). Therefore, you’ll need to create a script that performs changes and inserts new data (usually data for new dictionaries).
In our script, we’ve only created new objects and inserted data. All other commands are also allowed. For example, you could alter the existing table (adding or removing columns, adding properties) or even delete the table if it’s not needed anymore. Same stands for the data. You could perform not only INSERTs but also UPDATEs and DELETEs. In case you need to do that, you’ll make changes to the existing logic, so double-check everything (even things that you’ve already double-checked).
Conclusion
We can conclude that we achieved what we initially planned. This is great because now we have a much more sophisticated (but still very simple) model we can use to write more complex queries than we did so far. I won’t go into the nature of relations in this article. That shall be covered in the upcoming article, so stay tuned.
Table of contents
His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...
You can find him on LinkedIn
View all posts by Emil Drkusic
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020