0

I am working with a temporary table in Netezza that contains the columns id, gender, start_date, and end_date. I want to add a new column to this table that contains a default date of 2019-01-01 for all rows. The table to which I want to add this column is a local temp table, so ALTER TABLE does not work ("Error: Operation not allowed on a temp table"). To get around this, I created a new temp table as follows:

DROP TABLE new_temp_table IF EXISTS;
GO
SELECT id, gender, start_date, end_date, '2019-01-01' default_date
INTO TEMP TABLE new_temp_table
FROM old_temp_table;
GO

This new table is limited to 1000 rows by the SELECT...INTO syntax. My old table has 36 million rows. Is there a solution that would allow me to directly modify the old table to add the new default date column, or some other way to get around the 1000-row limit with SELECT...INTO?

sticky bit
  • 36,626
  • 12
  • 31
  • 42
Kellan Baker
  • 375
  • 3
  • 11
  • Why don't you just add the column to the statement creating the (first) temporary table? – sticky bit Feb 16 '20 at 17:12
  • not true: `This new table is limited to 1000 rows` . Or you are using something else than MS-SQL. – Luuk Feb 16 '20 at 17:14
  • @Luuk where is the MS-SQL mentioned ? – VBoka Feb 16 '20 at 17:18
  • @stickybit I didn't realize I could, since the table was being created from two permanent tables that didn't like me messing with them in other ways. But I just tried it, and it worked. Thank you for saving me from myself. For other students: I just added `, '2019-01-01' default_date` to `CREATE TEMP TABLE old_temp_table AS (SELECT DISTINCT id, gender, start_date, end_date FROM permanent_table_one pt1, permanent_table_two pt2)` – Kellan Baker Feb 16 '20 at 17:23
  • @KellanBaker . . . I didn't even realize that Netezza supported `SELECT . . . INTO` (it is not part of the syntax: https://www.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.dbu.doc/r_dbuser_select.html). Have you tried `CREATE TABLE AS`? – Gordon Linoff Feb 16 '20 at 18:53
  • @GordonLinoff yes, `SELECT...INTO` works in Netezza through DBVisualizer. – Kellan Baker Feb 17 '20 at 03:05
  • @KellanBaker . . . This is probably a DBVisualizer limitation. Use `create table as`. – Gordon Linoff Feb 17 '20 at 23:01

0 Answers0