4. SQL Statements : INSERT : INSERT Examples
 
Share this page                  
INSERT Examples
When inserting values into a Vector table, always insert many rows at a time rather than performing individual inserts. Consider using a staging table when single row inserts cannot be avoided.
CREATE TABLE employee_dim(
    employee_id INTEGER,
    emp_name VARCHAR(20),
    emp_rating DECIMAL(1,0),
    salary MONEY,
    manager VARCHAR(20),
    hiredate ANSIDATE,
    dept_no VARCHAR(12));
 
INSERT INTO employee_dim VALUES
(1,'Mike Sannicandro', 1,40000.00,'Al Obidinski','2013-10-22','Pers'),
(2,'Dave Murtagh', 4,20000.00,'Al Obidinski','2014-03-19',NULL),
(3,'Benny Barth', 9,5000.00,'Al Obidinski','2003-03-03','Stores'),
(9,'Al Obidinski', 7,50000.00,NULL,'1998-04-24','Dev'),
(5,'Dean Reilly', 4,50300.00,'Al Obidinski','2003-06-10','Dev');
 
DECLARE GLOBAL TEMPORARY TABLE YYMD00(
        rating DECIMAL(1),
        name VARCHAR(20),
        id BIGINT) ON COMMIT PRESERVE ROWS WITH NORECOVERY;
INSERT INTO YYMD00 (rating,name) VALUES (1,'Fred'),(2,'Toby'),(3,'Mary');
INSERT INTO YYMD00 SELECT emp_rating, emp_name, employee_id
   FROM employee_dim WHERE emp_rating = 2;
INSERT INTO YYMD00 WITH emp_cte AS (SELECT emp.emp_rating, emp.emp_name, emp.employee_id FROM employee_dim emp WHERE emp_rating = 3) SELECT * FROM emp_cte;
SELECT name FROM YYMD00 WHERE rating = 2;