Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

Introduction to Databases for the Web: Pt. 2 - Page 9

By Selena Sol

Adding Data

Granted, most of the work you will be doing with web databases will probably be viewing. For that, the SELECT-FROM-WHERE statements will be perfect for you. However, in many cases you will also want browsers to be able to modify your database. For example, perhaps you want them to be able to add themselves to a mailing list.

The most basic database manipulation is the INSERT operation that adds a new row to a specified table. The INSERT operation follows the basic syntax of:

INSERT INTO 
   table_name (column_names)
VALUES (values);

For example, we might want to add a new EMPLOYEE using:

INSERT INTO EMPLOYEES 
  (EMP_NUM, EMP_NAME, 
   EMP_COMMISSION, EMP_SALARY)
VALUES 
   (004, 'Lee Eng Lock', 
   '15%', 65000);

Of course, since we are assigning values for each column, we need not specify the column names. Thus, the following statement would work as well:

INSERT INTO EMPLOYEES 
VALUES 
(004, 'Lee Eng Lock', '15%', 65000);

We now have:

EMPLOYEES Table
EMP_NUM EMP_NAME EMP_COMMISSION EMP_SALARY
001 Lim Li Chuen 10% 90000
002 Lim Sing Yuen 20% 40000
003 Loo Soon Keat 20% 50000
004 Lee Eng Lock 15% 65000

Note that when we perform an INSERT, the incoming value must correspond to the data type defined for that column. Thus, CHARACTER strings must be enclosed in single quotes. Also note that if a row is inserted and some of the columns are not defined, the column will be filled with NULL. Finally, note that the order in which you specify columns and values does not matter, but if you change the order, you must specify column names.

It is also useful to remember that there is no concept of outside order to an SQL table. That is, you cannot know which row will be stored first or last in a table. Thus, you should be careful when assigning things such as unique ordered row id numbers.

Finally, know that you can easily include a SELECT-FROM-WHERE statement as the VALUE such as:

CREATE TABLE NEW_TABLE
   (EMP_NAME CHAR (20),
    CLIENT_NUM SMALLINT,
    PRODUCT_NUM SMALLINT);

INSERT INTO NEW_TABLE
   (EMP_NAME, CLIENT_NUM, PRODUCT_NUM)
   SELECT EMPLOYEES.EMP_NAME,
          SALES.C_NUM,
          SALES.P_NUM
   FROM EMPLOYEES, SALES
   WHERE SALES.E_NUM = EMPLOYEES.EMP_NUM;

The resulting table would look like:

NEW_TABLE Table
EMP_NAME CLIENT_NUM PRODUCT_NUM
Lim Li Chuen 002 001
Lim Sing Yuen 001 001
Lim Li Chuen 003 002


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date