Is it possible to insert multiple values using insert in sql

Question

Is it possible to insert in one single query multiple values into a table ? .

I have declared this table

declare global temporary table CFVariables
    (
        CF varchar(255)
    )
with replace ;

then i inserted values into the table

INSERT INTO qtemp.CFVariables ( CF ) VALUES
('F01' ), ('T01' ), ('U01' ), ('CIP' ), ('L01' )

Is it possible to not insert the values in qtemp.CFVariables table this way ? but like In ('F01' , 'T01' , 'U01' , 'CIP' , 'L01' )

Then , i declared my second table :

declare global temporary table xVariables
    (
        CFC  numeric(3),
        CF varchar(255)
    )
with replace ;

In this part i'm having a problem to insert into my table xVariables

I tried to use this to insert multiple values

INSERT INTO qtemp.xVariables ( CFC, CF ) VALUES
( 1, (select CF from  qtemp.CFVariables ))

My query field because i'm inserting more then one row to the table . How can i achieve this ?

I'm using IBM Db2

user15973370
– user15973370

2021-06-14 14:01:39 +00:00
Commented Jun 14, 2021 at 14:01 — user15973370
– user15973370, Commented Jun 14, 2021 at 14:01

Dri372 · Accepted Answer · 2021-06-14 14:03:30Z

1

Try

INSERT INTO qtemp.xVariables ( CFC, CF ) SELECT 1 AS CFC,CF from  qtemp.CFVariables;

answered Jun 14, 2021 at 14:03

Dri372

1,3315 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user15973370 Over a year ago

Is it possible to not insert the values in qtemp.CFVariables table this way ? but like in ('F01' , 'T01' , 'U01' , 'CIP' , 'L01' )

user15973370 Over a year ago

After VALUES clause ?

user15973370 Over a year ago

INSERT INTO qtemp.CFVariables ( CF ) VALUES WHERE CF IN ( ('F01' , 'T01' , 'U01' ,'CIP' , 'L01' )

Dri372 Over a year ago

Have you look our answer, there is no VALUES clause ... just add the WHERE clause at the end of the query given in the answer.

user15973370 Over a year ago

I have updated my question im trying to change the insert part of qtemp.CFVariables table to be more like a list IN ( ('F01' , 'T01' , 'U01' ,'CIP' , 'L01' )

|

Lajos Arpad · Accepted Answer · 2021-06-14 14:44:33Z

0

Try running an insert-select:

INSERT INTO qtemp.xVariables ( CFC, CF )
select 1, CF from  qtemp.CFVariables

To restrict the records to be inserted, you will need to do something like this:

INSERT INTO qtemp.xVariables ( CFC, CF )
select 1, CF 
from  qtemp.CFVariables 
where CF in ('F01' , 'T01' , 'U01' , 'CIP' , 'L01' )

edited Jun 14, 2021 at 14:44

answered Jun 14, 2021 at 14:03

Lajos Arpad

80.6k42 gold badges124 silver badges235 bronze badges

3 Comments

user15973370 Over a year ago

Is it possible to not insert the values in qtemp.CFVariables table this way ? but like in ('F01' , 'T01' , 'U01' , 'CIP' , 'L01' )

Lajos Arpad Over a year ago

@Kakoo16 please elaborate. From what you ask, to me it seems that the way this answer is inserting is good, but you want to restrict the set of records to be inserted to the listed items. Am I understanding you accurately?

user15973370 Over a year ago

Yes ! it's correct I want to restrict the set of records to be inserted to the listed items

Collectives™ on Stack Overflow

Is it possible to insert multiple values using insert in sql

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related