I’ve been recently reading the book Learning SQL by Alan Beaulieu and found that the example database script he supplies is very specific to MySQL. That’s understandable because the focus of the book is for that DBMS but I wanted to play around with the data on Microsoft’s SQL Server and that meant I needed to do some changes. For this article, I am going to explain how I converted the script he supplies here into this one, which will allow you to run it against SQL Server. Let’s begin with the tables.
I know what you are thinking…why on earth would I worry about dropping the tables (removing them) if I haven’t even created them yet? The reason I like to have these statements is because it allows me to reset the database to a fresh state, plus, I needed to do this anyway as I worked my way through the script, correcting any changes that were necessary. At times, the database would be left in a partially complete state and to ensure everything was still working properly, I would re-create everything. In addition, it’s worth noting that since some of the tables refer to each other, the order in which they are removed is really important because some depend on others for their existence. For this example, we need to remove them in this sequence:
drop table individual;
These need to be separate from the rest of the script since SQL Server doesn’t allow us to create tables and views on a create or replace basis and running these statements prior to the tables existing will give us an error like this:
Msg 3701, Level 11, State 5, Line 1 Cannot drop the table ‘individual’, because it does not exist or you do not have permission.
For those of you that are interested, it is possibel to detect if tables exist in SQL Server and only then drop them. To find out more, see this resource.
These don’t exist in SQL Server but are used quite a lot in the original script. Does this matter? Not for the data that Alan supplied but you can imagine a situation going forward where it might do with for example tens of thousands of customers. As an example, in MySQL, an
unsigned smallint ranges from 0 to 65535 whereas in SQL Server, that type only allows whole numbers between -32,768 and 32,767. If this would have been an issue, one option would have been to change all
smallint types to
int. I know what data is going to populate this, so that’s fine in my case and I won’t change the actual types but will remove all references to
In MySQL, this feature allows you to specify that the values in a field (usually a primary key) should be automatically added, incremented by a given amount each time and guaranteed to be unique. To replicate this in SQL Server, we need to change that to instead be
IDENTITY(1,1). In my example above, we are asking for the values to begin at 1 and increment by 1 each time. Here’s an example where I changed it in the
dept_id smallint not null identity(1,1)
Alan uses these quite a lot and generally they are a good idea (especially in programming languages), but unfortunately, they don’t exist in SQL Server. Instead we need to use the
CHECK constraint. As an example, let’s look at how the enumerated type was used originally in the
cust_type_cd enum('I','B') not null
For this, you can see that the
cust_type_id can be either an uppercase I or uppercase B, only. To replicate this in SQL Server, not only do we need to ensure those are the only valid values, but we must also specify the type.
cust_type_cd varchar(1) CHECK(cust_type_cd IN('I','B')) not null,
See the use of
VARCHAR(1)? I had to make an assumption with the enumerated types that their size wouldn’t exceed the maximum allowed value of those checks, but with the data given, that’s a fairly safe decision. We also need to make similar changes to
txn_type_cd in the
One of the tables that Alan named was
TRANSACTION. That’s a reserved word in SQL Server, so instead, let’s change it to
ACC_TRANSACTION and remember to use that updated name when we come to populate it.
In MySQL, this will create a floating point value which is at most 10 digits long with 2 digits after the decimal point. To replicate that in SQL Server, we need to use
FLOAT(53) which you can find more about, here.
This too can be mapped to an equivalent type, and in this case, that would be
In MySQL, there is a feature known as a temporary table which in this case, is used to assist with some referential integrity.
create temporary table emp_tmp as
To mimic this, I instead created a view and once used, removed it.
create view emp_tmp as
There was one statement in the script that I didn’t think was required and that was an alteration which was attempted against the
EMPLOYEE table, adding a foreign key. Since that already existed in the creation of table
EMPLOYEE, I just commented it out.
alter table employee add constraint fk_e_emp_id
Phew! That’s the table schema fixed. Now let’s look at populating the data.
When inserting values into auto incremented fields (
IDENTITY(1,1)) we don’t need to insert
NULL as Alan did in his MySQL script. Instead, all we need to do is to ignore it and it will be populated automatically. Here’s an example change I made for the
DEPARTMENT table which went from this:
insert into department
insert into department
You can see that I removed the ID field reference and the
NULL. The next step was to do the same for the tables:
ACCOUNT. We’re almost done. The last thing is to remove another MySQL specific statement.
In MySQL, it’s possible to limit the results of a query to be at most ‘n’ rows long using the
LIMIT statement. That’s command isn’t available in SQL Server but we can do something similar using
TOP. So, we go from this:
select b.branch_id, e.emp_id
select top 1 b.branch_id, e.emp_id
This feature is used in quite a few places, so you will need to remove all references to
limit 1 and instead, place
top 1 at the beginning of the
SELECT statement. One finally run through of the script and all was well! You can find the link to download the new script at the top of this article but do read my instructions inside, carefully. Things need to be run in a particular order and some statements can’t be run with others at the same time. Also, don’t forget to actually create your database in the server management studio - I haven’t covered that here, but assume you know how.
Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com