Insert rows into a table by 'With' clause

[ChandanM]

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.

[Rabbit]

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.

[ChandanM]

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.

[Rabbit]

Please post the code with the insert.

[ChandanM]

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
)

[Rabbit]

The with clause has to come first. Like in your first post.

[ChandanM]

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. :-(

[Rabbit]

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.

[ChandanM]

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
)

[Rabbit]

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?

[ChandanM]

The query does not execute and it gives the following error message:

SQL0199 - Keyword INSERT not expected. Valid tokens: ( SELECT VALUES.
State:37000,Nat ive:-199,Origin:[IBM][System i Access ODBC Driver][DB2 for i5/OS]

[Rabbit]

What version of DB2 are you using?

[ChandanM]

The version is V6R1M0.

[ChandanM]

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.

[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:

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.