473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 19707
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

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

Similar topics

11
by: grumfish | last post by:
I'm trying to add a row to a MySQL table using insert. Here is the code: connection = MySQLdb.connect(host="localhost", user="root", passwd="pw", db="japanese") cursor = connection.cursor()...
1
by: smile | last post by:
Hi All I want to insert rows from a table in a server into another table in another server using INSERT SELECT command. For example : INSERT INTO Server1.database1.dbo.Tab1 SELECT * FROM...
0
by: Alexander Vlasenko | last post by:
Please CC me, I am not subscribed. An imaginary SQL statement INSERT INTO table FETCH ... FROM cursor; looks almost the same as currently available INSERT INTO table SELECT ...; I tried it...
4
by: RG | last post by:
Using VB.NET, How do I insert rows from a SQL Server table into an Access table with the same structure (and also the reverse, from Access to SQL)? I’m new to this, so here’s what I’ve...
2
by: Charles Wilt | last post by:
I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access via a linked server from SQL Server 2000. The following select works fine: select * from...
2
by: M Bourgon | last post by:
I have a file I'm trying to do some non-set-based processing with. In order to make sure I keep the order of the results, I want to BULK INSERT into a temp table with an identity column. The spec...
0
by: Robert Bravery | last post by:
HI all, VS 2005 C# MSSQL2005 I have a parent child grandchild related tables. Claimheader, claim (deteails), claimfinancialloss When ever I try to add row to claimfinancialloss table, I get...
2
by: gnewsgroup | last post by:
I am new to the asp.net Table web control. I am using it to customize my presentation of data. I cannot implement the idea with DataGrid or GridView. Basically, I would like to have something...
2
by: ramzansadiq | last post by:
Hi guru's, I m unable to find how to Insert a primary key which is also an autonumber field into Table. My query is as below.It is inserting record if i m hardcoding the ID Field. It is VBA Access...
1
by: rdsandy | last post by:
Hi, I have a table Risk, a table Mitigation and a table RiskArchive. I am taking the RiskID, Criticality and MitigationPlan fields from Risk, and MitigationActionID from Mitigation and inserting...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.