Converting MySQL Schema and Data Statements to SQL Server

database

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.

Dropping 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:

1
2
3
4
5
6
7
8
9
10
11
drop table individual;
drop table officer;
drop table acc_transaction;
drop table account;
drop table product;
drop table product_type;
drop table business;
drop table customer;
drop table employee;
drop table department;
drop table branch;

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.

Unsigned Types

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 unsigned.

AUTO_INCREMENT

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 DEPARTMENT table:

1
dept_id smallint not null identity(1,1)

Enumerated Types (Enum)

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 CUSTOMER table:

1
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.

1
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 cust_type_cd in CUSTOMER and txn_type_cd in the TRANSACTION table.

Reserved Words

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.

Data Types

DOUBLE(10,2)

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.

FLOAT(10,2)

This too can be mapped to an equivalent type, and in this case, that would be FLOAT(24).

Temporary Tables

In MySQL, there is a feature known as a temporary table which in this case, is used to assist with some referential integrity.

1
2
3
4
5
6
create temporary table emp_tmp as
select emp_id, fname, lname from employee;

/* Later, this is dropped with... */

drop table emp_tmp;

To mimic this, I instead created a view and once used, removed it.

1
2
3
4
5
create view emp_tmp as
select emp_id, fname, lname from employee;

/* and is removed later, with... */
drop view emp_tmp;

Redundancy

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.

1
2
alter table employee add constraint fk_e_emp_id
foreign key (superior_emp_id) references employee (emp_id);

Phew! That’s the table schema fixed. Now let’s look at populating the data.

Adding Data Which Uses the IDENTITY fields

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:

1
2
3
4
5
6
insert into department 
(dept_id, name)
values 
(null, 'Operations'),
(null, 'Loans'),
(null, 'Administration');

to:

1
2
3
4
5
6
insert into department 
(name)
values 
('Operations'),
('Loans'),
('Administration');

You can see that I removed the ID field reference and the NULL. The next step was to do the same for the tables: BRANCH, EMPLOYEE, CUSTOMER, OFFICER and ACCOUNT. We’re almost done. The last thing is to remove another MySQL specific statement.

LIMIT

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:

1
2
3
select b.branch_id, e.emp_id 
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Woburn' limit 1

to this:

1
2
3
select top 1 b.branch_id, e.emp_id 
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Woburn'

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