3.4 Insert Statement

00:00

All right. Welcome back to Mali, Joe. Three sequel programming. This is less than one sequel Statements were in Sub Lesson 1.5 The insert statement. Now for this lesson, we're going to focus on a smaller table, and the reason we're going to do that is just so that we do not have to deal with a large number of columns. Of course,

00:20

you could take this insert statement, apply it to a larger table, and you would just need to include the additional columns that you had to account for. Now, the table we're focusing on is this one e p employees position cavey extension,

00:34

so you can find that by just filtering it down on the top

00:38

and then finding it and given it a double click.

00:42

Let's go ahead and take a look at the diagram

00:45

we could see. It is indeed a smaller table with a total of seven fields.

00:51

Now this table is used by Aqim Attica to facilitate customization. Sze

00:57

Ah, if you had a company with very unique requirements that they couldn't account for in the core product, they could have a programmer create custom fields that would allow the consumer to store data in those custom fields

01:11

and interact

01:14

with that data in a way that they really couldn't plan for

01:18

in their core product. And the reason for that is just

01:22

you run into a bunch of different company types that just have different work flows. They store different data, they're interested in different data, and the only way to really facilitate that is to leave room for customization.

01:34

But

01:34

we're not focusing on that. We're focusing on how to do an insert statement, so let's get to it.

01:40

So if we left click on that table name, we right Click

01:44

de Beaver has an excellent feature called Generate Sequel,

01:47

and we will go to that feature and we will click the insert statement

01:52

and this will pop up a box that shows you what the insert statement for that table looks like. And we can see that this statement is similar to the update and select statement and that we have

02:05

and sir, into table name.

02:08

These column names these values, so it's table name of columns values.

02:15

So we're gonna go ahead and copy that

02:19

we're going to close it,

02:21

and we're going to open up a new sequel editor.

02:24

Let's go ahead and paste that in to that sequel editor.

02:29

Now this has the full name, which is database name, table name.

02:32

Now we have

02:34

or we should have the ACU Matic, a database said as the active database. So we actually don't need that doesn't matter if it's there or not. If you don't have it, said, is the active that evasive? You will need it there so that the statement can run correctly.

02:49

So let's go ahead and insert some data. Let's just say company I d. 99 record I d.

02:54

Anything

02:58

feel name anything.

03:00

Ah, value in America will just say 123

03:05

value date. Let's do today

03:10

or today for me

03:12

might be a different day for you. We'll probably be a different for you.

03:15

Value String 123

03:17

Call your tax 123

03:21

string in Texas. Very similar ideas. It's probable that a programmer could decide to leave one blank or not, depending if he wanted to save space. But a string in texts are very similar in concept,

03:34

so we filled this in. Let's go ahead and run the statement

03:39

and it says it updated one row

03:43

and its reporting that it was successful.

03:45

So let's go take a quick look

03:47

by giving that table a double click

03:51

and clicking on the data.

03:53

Refresh.

03:55

Okay, so there we go. There's Thedot A. We just applied, we correctly inserted into the table,

04:01

and it worked just fine.

04:03

Now something keep in mind when you are inserting or updating data, select snot is

04:10

Ah, what's the word? I would say, um, dangerous

04:15

within certain update

04:16

if especially if you're going directly into the database, you have to be very confident

04:24

that you are applying the correct changes.

04:28

In fact, you normally will want to be sure to back up any data that you're changing so that you can revert back if necessary.

04:36

Um, and the reason is is that when you're interacting with the database, there's

04:42

if there's no constraints, there's literally no rules on what you can do to the database. So,

04:46

as a quick example, I put company I D 99. That company does not exist in this database, so it is a broken relationship. It's this record that sitting out here in this table that doesn't relate to anything, and this would be considered bad

05:04

just because Dada they insert into a database. You want those

05:09

relationships to carry throughout the database so that things make logical sense and when these applications get set up, their set up in ways that prevent,

05:16

um, things like this from happening. For example, if you were going to insert a record from the front of an application, the front and application would on Lee let you select from a list of companies that exist. And if you wanted to use company I D 99 you would have to go create a company 99 before you used it.

05:34

That isn't the case here. We could just insert

05:36

anything we want and the database will accept it. So that's just a

05:42

small thing to keep in mind

05:44

when working with databases, working with inserts and updates statements.

05:47

So outside of that, you can see the insert statement was pretty easy. You just insert into the table, name

05:54

the column names, and then the values you want. Insert into those columns now. There were no constraints on this table if there had been.

06:03

We can see that in the diagram that this table has no constraints tying it to any other table. If there had been a constraint,

06:12

whatever constraint there was in that table

06:15

might have required it to exist. For example, an easy constraint would be a company table where Company 99 would have to exist in the company table before we inserted into

06:25

the employees of position table. But that constraint doesn't exist. So we were able to insert company

06:29

that didn't exist?

06:30

No.