insert into using a select/union
insert into using a select/union
(OP)
I am trying to populate a temp table from a list of values I get back from a Java method call. I'm trying to do a batch insert, but cannot seem to find the right syntax. I tried
SELECT 'constant1' n from table
UNION ALL
SELECT 'constant2' n from table
INTO TEMP a;
The syntax works, but I am inevitably going to exceed the maximum statement length and I cannot use the same clasue again since the temp table already exists. I tried the followig, but I keep getting a syntax error:
INSERT INTO a
SELECT 'constant1' from table
UNION ALL
SELECT 'constant2' from table;
Any ideas? I really do not want to have to generate a separate insert statement for every value in my list.
Also, my table is guaranteed to only ever have one row so I just get back the singlular constant value. Is there a better way to force only one row instead of having to rely on the table only having one row? FIRST 1 is not supported within an INSERT or INTO.
SELECT 'constant1' n from table
UNION ALL
SELECT 'constant2' n from table
INTO TEMP a;
The syntax works, but I am inevitably going to exceed the maximum statement length and I cannot use the same clasue again since the temp table already exists. I tried the followig, but I keep getting a syntax error:
INSERT INTO a
SELECT 'constant1' from table
UNION ALL
SELECT 'constant2' from table;
Any ideas? I really do not want to have to generate a separate insert statement for every value in my list.
Also, my table is guaranteed to only ever have one row so I just get back the singlular constant value. Is there a better way to force only one row instead of having to rely on the table only having one row? FIRST 1 is not supported within an INSERT or INTO.
Talk To Other Members
RE: insert into using a select/union
i tried both of your suggestions and never get an error.
Are you sure you do not suppress any details?
If you select a constant you will have to provide a pseudo-column-name, which i guess should be the "n" in your statement.
please check if you omitted something.