SQL RELOADING DATABASE

I have needed to learn more about SQL for a while. I’ve installed SQL Express and SSMS 20 at home, and I’m building a database to help me track reloading data.

I used ChatGPT to help me design the structure, combining what it already knows about reloading with my personal preferences. (AI tip… don’t explain to it what it already knows!) I tried to type the queries, rather than copy and paste, as that’s how I learn best. (I heard an argument that you should copy and paste for efficiency’s sake, and I agree with that unless I’m actually trying to learn.)

The first mistake I made was getting caught up in the tables and forgetting to make the database! Creating the database was easy:

CREATE DATABASE ReloadingDB;
Go

USE ReloadingDB;
GO

I then created the first table, Calibers. This table has only two fields.

CREATE TABLE Caliber (
  CaliberID INT PRIMARY KEY AUTO-INCREMENT,
  CaliberName VARCHAR(50) NOT NULL
);

The idea was that the Caliber ID would be automatically created and incremented, but it turns out that SQL Express needs a slightly different terminology for that:

CaliberID INT PRIMARY KEY IDENTITY(1,1)

My understanding is that (1,1) means start at one, increment by one.

In hindsight, I’m not sure that having CaliberID be the Primary Key was the best move. We’ll see if I pay for that later when doing joins. I think using Caliber itself as the Primary Key might have prevented duplication. But, there may be other ways to do that and other reasons to have an “ID” field that I don’t know about yet.

At this point I began adding data to the table. I used this:

INSERT INTO Caliber (CaliberName) VALUES ('9mmm Luger'); 

But I didn’t want to do this over and over, so I asked ChatGPT to help me import the calibers from a .TXT. I had some real trouble with that! I must have missed where you select the destination table, and it just created a new table. (I might consider using that for creating a table later… I figure a lot of DBs probably start out as spreadsheets anyway)

I then tried to copy the contents of the new table back to the Caliber table. I’m not sure if I did it wrong or if ChatGPT steered me wrong, so I decided that it wasn’t helping me to learn by wondering which it was. I removed BOTH tables with the below query, and started over. (Not much time was invested, and I thought the repetition would be more valuable at this point.)

DROP TABLE Caliber; 

I then built back the Caliber table. This time I decided not to give up on a quick way to enter calibers, but I didn’t want to try the import again. I asked ChatGPT about any sort of “Powershell-like” way to create a variable, so I could just change that and re-run the query to add data. ChatGPT suggested this:

DECLARE @CaliberName NVARCHAR(50);
SET @CaliberName = '9mm';

INSERT INTO Caliber (CaliberName)
VALUES (@CaliberName);

However, ChatGPT also suggested an actual Powershell script, and I decided that was a much better option. I had already been thinking about some kind of “front end” application for this DB, and since I understand Powershell, I figured that’d be a good place to start. (I’ll not cover the details of that in this post… trying to stay on task with SQL! Python is also calling my name for this…)

At this point, I started to wonder were all SQL “commands” called “queries,” and ChatGPT told me that either would be understood but there are differences. I decided not to get into them at this point, but I wanted to put that in here to remind me to look into it later.

I decided to move on to the Bullet table next. Creating the table I had to deal with a new type of variable. The diameter will always be a decimal between 0 and .5, and always measured in thousands of an inch.

Diameter DECIMAL(3,0)

Evidently this format says “this is a three digit number where 0 of the digits are right of the decimal point.” EDIT: Apparently I did this backwards… will have to fix.

Similarly, the weight measured in grains will always be a whole number:

BulletWeight DECIMAL(3,0)

Both of the diameter and bullet weight fields are also set to NOT NULL, but there’s also a field for type that can be null.

Inserting data into this table made more sense to me than how I was inserting data into the Caliber table. (Without Powershell. I’ll make a Powershell function for this later.) In this case, the values were kind of in an array, I guess:

INSERT INTO Bullet (Diameter, BulletWeight, BulletType) 
VALUES
	(.223, 55, 'FMJ'),
	(.264, 123, 'SST'),
	(.264, 123, 'OTM'),
	(.308, 150, 'Interlock'),
	(.223, 55, 'FMJ'),
	(.310, 123, 'PSP');

I’ll continue on with some other tables in the future, and I hope to create some views that will bring the load data together for me.