Dynamic SQL

[RicMtl]

I am new to DB2 SQL. I want to create a dynamic SQL statement like: INSERT INTO destinationtable SELECT * FROM sourcetable

I cannot use an EXECUTE IMMEDIATE statement for this because it contains a SELECT. I come from a SQL Server backround and it was easy to execute dynamic SQL by simple storing it in a string and then using EXEC. Is there a way to do this in DB2?

Thanks everyone!

Ric

 

[Mercury2]

A subselect is allowed. The SELECT of your stm is in a subselect clause.

sqlstm = 'insert into destinationtable (select * from sourcetable [where ...])'
/Exec sql Execute immediate :sqlstm
/End-exec

Or maybe better yet:

sqlstm = create table destinationtable as (select * from sourcetable [where ...]) with data'
/Exec sql Execute immediate :sqlstm
/End-exec

 

[gregsimpson]

Mercury2,

Agree with your first idea but have a question on your second.

Have you managed to get your second idea working with DB2? The last time I tried this it failed to work? Managed to get the same working with Oracle but not DB2, I was just wondering if there is something I missed in the DB2 approach?

Thanks
Greg

 

[Mercury2]

What version and platform are you on ?
I gave again a try on my OS/400-DB2 and my create table worked like a charm.