Emil Drkusic

Learn SQL: SQL Scripts

January 21, 2020 by

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:

sql script - old model

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.

new data model

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:

SQL Server backup

After choosing the backup option, you’ll get the pop-up as on the picture below:

SQL Server backup

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:

SQL Server restore

You’ll need to enter the name of the restored database (our_first_database_old) and click OK:

SQL Server restore

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:

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:

SQL Server - script execution outcome

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 - result

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

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions

Emil Drkusic
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. 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

10,523 Views