Hi,
I have a database from which I am retrieving first 10 rows for each group using the 'With' table clause.
e.g - with data
-
(
-
select row_number() over (partition by state) as num, state id, name, ssn from state_population
-
)
-
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.
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: - INSERT INTO table (field list)
-
WITH data AS (select query)
-
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
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.
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.
Please post the code with the insert.
- insert into table1 (stid, pname, pssn)
-
(
-
with data
-
(
-
select row_number() over (partition by state) as num, stateid, name, ssn from state_population
-
)
-
select stateid, name, ssn from data where num <= 10
-
)
The with clause has to come first. Like in your first post.
I tried the following 2 alternatives:
1. - with data
-
(
-
insert into table1 (stid, pname, pssn)
-
(
-
select row_number() over (partition by state) as num, stateid, name, ssn from state_population
-
)
-
select stateid, name, ssn from data where num <= 10
-
)
2. - with data
-
insert into table1 (stid, pname, pssn)
-
(
-
(
-
select row_number() over (partition by state) as num, stateid, name, ssn from state_population
-
)
-
select stateid, name, ssn from data where num <= 10
-
)
Both did not work. :-(
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.
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. -
with data
-
(
-
select row_number() over (partition by state) as num, stateid, name, ssn from state_population
-
)
-
insert into table1 (stid, pname, pssn)
-
(
-
select stateid, name, ssn from data where num <= 10
-
)
-
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?
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]
What version of DB2 are you using?
I found a better way to do it, by removing the WITH statement itself. -
insert into table1
-
(
-
select stateid, name, ssn from
-
(
-
select row_number() over (partition by state) as num, stateid, name, ssn from state_population
-
)
-
as data
-
where num <= 10
-
)
-
Thanks for your time and help.
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: - INSERT INTO table (field list)
-
WITH data AS (select query)
-
SELECT field list FROM data;
I had to go to the manual to find that as I didn't have your version of DB2.
Thank you for your efforts. :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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()...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |