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;