INSERT Examples
1. Add a row to an existing table.
INSERT INTO emp (name, sal, bdate)
VALUES ('Jones, Bill', 10000, 1944);
2. Insert into the job table all rows from the newjob table where the job title is not Janitor.
INSERT INTO job (jid, jtitle, lowsal, highsal).
SELECT job_no, title, lowsal, highsal
FROM newjob
WHERE title <> 'Janitor';
3. Add a row to an existing table, using the default columns.
INSERT INTO emp
VALUES ('Jones, Bill', 10000, 1944);
4. Use a structure to insert a row.
/* Description of table employees from
database deptdb */
EXEC SQL DECLARE employees TABLE
(eno SMALLINT NOT NULL,
ename CHAR(20) NOT NULL,
age SMALLINT,
jobcode SMALLINT,
sal FLOAT NOT NULL,
deptno SMALLINT);
EXEC SQL BEGIN DECLARE SECTION;
emprec
int eno;
char ename[21];
int age;
int job;
float sal;
int deptno;
EXEC SQL END DECLARE SECTION;
/* Assign values to fields in structure */
eno = 99;
ename = "Arnold K. Arol”;
age = 42;
jobcode = 100;
sal = 100000;
deptno=47;
EXEC SQL CONNECT deptdb;
EXEC SQL INSERT INTO employees VALUES (:emprec);
EXEC SQL DISCONNECT;
5. Insert explicit values into a t1 row regardless of the identity column definition. Without the OVERRIDING clause, this statement generates a syntax error.
INSERT INTO t1 OVERRIDING SYSTEM VALUE VALUES(1, 2, 3);
6. Insert four rows into table emp.
INSERT INTO emp (empno, deptno, sal)
VALUES (698, 300, 850), (840, 100, 4400), (789, 400, 6500), (299, 500, 3900);