Why is an INSERT INTO command in Access SQL not repeatable?

[peddy00]

Hi,

I'm pretty much brand new to using databases. I came across a useful (so far) online course for learning the basics of SQL. It's a bit frustrating at times because some of the examples it uses aren't compatible with Access (I'm using 2007). For example, it gives the following as an example of a code for inserting a row of data into an existing table:

<code>create table employee
 (first varchar(15),
  last varchar(20),
  age number(3),
  address varchar(30),
  city varchar(20),
  state varchar(20));</code>

When I try to run that query in Access, it produces an error. When I replace number(3) with byte, it works fine. (If you want to suggest why number(3) might not be working for me, I'm open to hearing you out, but that's not why I'm writing.)

Once I've created that table, the course also has an example for how to append a row of data to the table:

<code>insert into employee
  (first, last, age, address, city, state)
  values ('Luke', 'Duke', 45, '2130 Boars Nest', 
          'Hazard Co', 'Georgia');</code>

When I run that code once, it fills in the first row of the table as expect. If I then delete the row that it just produced, and run the code again, nothing happens. Why is that?

Thank you for your help.

Peter

 

[carpy1985]

From my own experience an insert into creates a new table - to run the same query again I'd DROP <TABLENAME> first. Then run the same query again.

 

[SydneyGeek]

A couple of things are happening here.

1. The SQL you are using has data types for a "big" database, eg SQL Server or Oracle. The data types don't map exactly to Access.
For numbers you would use:
Boolean for Yes/No
Byte for integers to 255
Integer for integers to 32K
Long for integers to 2 billion

That really mattered when memory was limited. I tend to use Boolean and Long.

If you need decimals, use:
Single or Double (most people use Double because the precision is better, as is the number range. Double is actually the default number type for a numeric value in Excel)

For currency, use Currency. It's a decimal data type; Single and Double are binary.

Also, although Access understands Varchar the text data types are Text (to 255 chars) or Memo (to 64K chars).

2. Running the query a second time you apparently don't see the record in the table. That's because you haven't refreshed the view; select the Refresh All option on the ribbon, or close and re-open the table.

@carpy1985: INSERT INTO will create a new table if no table exists -- but if one is there, you will append data instead. That's a quirk of Access. In Oracle, for example, you can create a new table from data by using the syntax
CREATE TABLE Sometable
AS
(SELECT foo
FROM bar)

The INSERT INTO syntax will fail in Oracle if the table doesn't exist.

Denis

 

[carpy1985]

In access insert into always deletes the table if I already have one by the same name - is it meant too?

But in oracle the insert into would append like I thought it should in access lol thanks

 

[SydneyGeek]

When I ran the exact code that peddy00 listed, I got the record appended to an existing table, ie 2 identical records.

Try creating a make-table query and an append query from the same starting data, and see what the difference is in the syntax.

Denis

 

[xenou]

To the best of my knowledge Access will not delete existing tables when using INSERT INTO ... (I use this all the time and have never witnessed such behavior). Access does have an alternative "Make Table" syntax which is SELECT ... INTO. This latter would be the one that deletes existing tables if they exist. See the Access help on "Insert Into Statement" and "SELECT INTO Statement"

INSERT INTO Statement (Microsoft Access SQL)*[Access 2007 Developer Reference]
SELECT


@peddy00: to tack on to SydneyGeek's explanation of datatypes, you could say that every database has it's own "flavor" of SQL. There are a lot of similarities across databases, but many differences as well. When you learn SQL, you (hopefully) learn all the basics of the languages that databases generally share in common - the essentials of the SQL language. But once you start working with a database (of a specific kind) you also have to be aware of features that are unique to that piece of software. It may be a bit of a weakness of the tutorial that you are using that it doesn't really say what database platform it is targeting, but probably its assuming an "enterprise" database such as SQL Server, MySQL, Oracle, or PostGRE. With a small amount of alteration, it still applies to Access, however.

As you progress in your learning, you'll probably like to try setting up a server based database such as MySQL (which has a free community edition) or SQL Server (which also has a free "express" edition). For that matter, Oracle also has an express edition, and PostGRE is 100% free and open-source. However, Access is still a very powerful little database in its own right. I get tons of productivity out of Access, and it's very easy to use. Another benefit of Access, as you get to know it, is that you can in fact use the Access platform for your queries, forms, and reports, while still (under the hood) connecting Access to a server-based database. Which gives you the best of both worlds.

 

[SydneyGeek]

Thanks xenou.

Forgot briefly about INSERT INTO vs SELECT INTO.

Denis

 

[peddy00]

Thanks everyone for the help.

Denis, I'm really green, so I didn't even know there was a refresh button until you pointed it out. The refresh button is revealing the appended rows that the query is making. Oh, and your signature about self-preservation is really good!

Xenou, thanks for the clarifying that the different programs are slightly different.

 

[SydneyGeek]

You're welcome

By the way, that tutorial looks pretty good but if you prefer to have a book to scribble in the best I've seen recently is Simply SQL (Rudy Limeback; SitePoint). It's also available as an e-book, but what I like about it is the explanation on why SQL runs as it does, and the order in which SQL commands are processed.

Denis