469,328 Members | 1,274 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

Insert rows into a table by 'With' clause

Hi,

I have a database from which I am retrieving first 10 rows for each group using the 'With' table clause.
e.g
Expand|Select|Wrap|Line Numbers
  1. with data
  2. (
  3. select row_number() over (partition by state) as num, state id, name, ssn from state_population
  4. )
  5. select * from data where num <= 10
I want to insert the rows retrieved from above query into another table.

Can someone help me with this?

Thanks in advance.
Sep 25 '13 #1

✓ answered by Rabbit

I wouldn't say better necessarily, they're pretty much the same thing just with different syntax. For future reference, this is how you would do it using the WITH clause:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO table (field list)
  2. WITH data AS (select query)
  3. SELECT field list FROM data;
I had to go to the manual to find that as I didn't have your version of DB2.

15 18412
Rabbit
12,516 Expert Mod 8TB
The syntax for inserting data into a table remains the same. You just now have that extra bit at the top, your WITH clause.

By the way, I don't think DB2 has a row_number() function. I think you're in the wrong forum.
Sep 25 '13 #2
Hi Rabbit.

I m using Query Tool to query the database (DB2 for OS400), and the above query works. However, when I add the insert query on top of it, it throws an error.

Keyword WITH not expected. Valid tokens: ( SELECT VALUES.
Sep 25 '13 #3
Rabbit
12,516 Expert Mod 8TB
Please post the code with the insert.
Sep 25 '13 #4
Expand|Select|Wrap|Line Numbers
  1. insert into table1 (stid, pname, pssn)
  2. (
  3. with data
  4. (
  5. select row_number() over (partition by state) as num, stateid, name, ssn from state_population
  6. )
  7. select stateid, name, ssn from data where num <= 10
  8. )
Sep 26 '13 #5
Rabbit
12,516 Expert Mod 8TB
The with clause has to come first. Like in your first post.
Sep 26 '13 #6
I tried the following 2 alternatives:

1.
Expand|Select|Wrap|Line Numbers
  1. with data
  2. (
  3. insert into table1 (stid, pname, pssn)
  4. (
  5. select row_number() over (partition by state) as num, stateid, name, ssn from state_population
  6. )
  7. select stateid, name, ssn from data where num <= 10
  8. )
2.
Expand|Select|Wrap|Line Numbers
  1. with data
  2. insert into table1 (stid, pname, pssn)
  3. (
  4. (
  5. select row_number() over (partition by state) as num, stateid, name, ssn from state_population
  6. )
  7. select stateid, name, ssn from data where num <= 10
  8. )
Both did not work. :-(
Sep 26 '13 #7
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code or formatted data. This is your third and final warning.

The entire WITH clause needs to stay the same as it is in your first post. Not just the first line of the WITH clause. In your first post, everything from line 1 through 4 is part of the WITH clause. The only line you're supposed to change is the last line.
Sep 26 '13 #8
Hi Rabbit,

Sorry I am new to this site, and did not see your posts about formatting the code. Will ensure I format the code part going forward.

Anyway, the option you have mentioned was tried the very first time before posting this query. That one too isn't working.

Expand|Select|Wrap|Line Numbers
  1. with data
  2. (
  3. select row_number() over (partition by state) as num, stateid, name, ssn from state_population
  4. )
  5. insert into table1 (stid, pname, pssn)
  6. (
  7. select stateid, name, ssn from data where num <= 10
  8. )
  9.  
Sep 26 '13 #9
Rabbit
12,516 Expert Mod 8TB
Works fine for me.

You have to be more specific when you say it doesn't work. What are the symptoms and what is the full error text?
Sep 26 '13 #10
The query does not execute and it gives the following error message:

SQL0199 - Keyword INSERT not expected. Valid tokens: ( SELECT VALUES.
State:37000,Native:-199,Origin:[IBM][System i Access ODBC Driver][DB2 for i5/OS]
Sep 26 '13 #11
Rabbit
12,516 Expert Mod 8TB
What version of DB2 are you using?
Sep 26 '13 #12
The version is V6R1M0.
Sep 26 '13 #13
I found a better way to do it, by removing the WITH statement itself.
Expand|Select|Wrap|Line Numbers
  1. insert into table1
  2. (
  3. select stateid, name, ssn from
  4. (
  5. select row_number() over (partition by state) as num, stateid, name, ssn from state_population
  6. )
  7. as data
  8. where num <= 10
  9. )
  10.  
Thanks for your time and help.
Sep 26 '13 #14
Rabbit
12,516 Expert Mod 8TB
I wouldn't say better necessarily, they're pretty much the same thing just with different syntax. For future reference, this is how you would do it using the WITH clause:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO table (field list)
  2. WITH data AS (select query)
  3. SELECT field list FROM data;
I had to go to the manual to find that as I didn't have your version of DB2.
Sep 26 '13 #15
Thank you for your efforts. :-)
Sep 26 '13 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by grumfish | last post: by
1 post views Thread by smile | last post: by
reply views Thread by Robert Bravery | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.