Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

insert query

--I've an existing table with data as below.
CREATE TABLE dba.t_states(state_id int,ev_id int,state_name varchar(25))
INSERT INTO  dba.t_states Values(2,1674,'VIP')
INSERT INTO  dba.t_states Values(4,1674,'Junior')
INSERT INTO  dba.t_states Values(8,1674,'Senior')
INSERT INTO  dba.t_states Values(1,1680,'C1')
INSERT INTO  dba.t_states Values(5,1680,'C2')
INSERT INTO  dba.t_states Values(7,1680,'C3')
SELECT * FROM dba.t_states
--I've this another table below where, in qual_id column I need to insert 1 for 1 event, 2 for other event ans so on
--I need a query which should display data as shown below.
CREATE TABLE dba.t_qual (qual_id int,state_id int)
INSERT INTO dba.t_qual VALUES(1,2)-- so for event 1674, qual_id =1 and state_id =2
INSERT INTO dba.t_qual VALUES(1,4)-- so for event 1674, qual_id =1 and state_id =4
INSERT INTO dba.t_qual VALUES(1,6)-- so for event 1674, qual_id =1 and state_id =4
-------------------------------------------------------------------------------------------------------
INSERT INTO dba.t_qual VALUES(2,1)-- so for event 1680, qual_id =2 and state_id =1
INSERT INTO dba.t_qual VALUES(2,5)-- so for event 1680, qual_id =2 and state_id =5
INSERT INTO dba.t_qual VALUES(2,7)-- so for event 1680, qual_id =2 and state_id =7
0
cottage125
Asked:
cottage125
1 Solution
 
wilcoxonCommented:
If I understand what you want then this should do it:

select s.ev_id, q.qual_id, q.state_id
from dba.t_states s, dba.t_qual q
where s.state_id = q.state_id

Open in new window


If this is not what you want, please explain what you are looking for more clearly.
0
 
Monica PCommented:
you want insert statement to be generated dynamically?
0
 
awking00Commented:
create table dba.t_qual(qual_id, state_id) as
select dense_rank() over (order by ev_id) as qual_id, state_id
from dba.t_states;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awking00Commented:
create table dba.t_qual(qual_id, state_id) as
select dense_rank() over (order by ev_id) as qual_id, state_id
from dba.t_states;
0
 
cottage125Author Commented:
I want insert statement which has some sort of logic so that it will insert qual_id =1 for any specific event.
For other event qual_id should be different than 1. I dont mind adding one more column in t_qual table if needed.
0
 
awking00Commented:
Perhaps I still don't understand what you need. This will insert a 1 for the first (numerically) ev_id into the qual_id, a 2 for the second ev_id into the qual_id, etc.
insert into dba.t_qual(qual_id, state_id)
select dense_rank() over (order by ev_id) as qual_id, state_id
from dba.t_states;

SQL> select * from t_states;
  STATE_ID      EV_ID STATED_NA
---------- ---------- ---------
         2       1674 VIP
         4       1674 Junior
         8       1674 Senior
         1       1680 C1
         5       1680 C2
         7       1680 C3

SQL> select dense_rank() over (order by ev_id) qual_id, state_id
  2  from t_states;
QUAL_ID   STATE_ID
----------    ----------
         1          2
         1          4
         1          8
         2          1
         2          5
         2          7
0
 
cottage125Author Commented:
okay thanks. I think thats what i need.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now