Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • Last Modified:

db2 sql: Is it possible to create table from an existing view, copying data too?

db2 sql: Is it possible to create a table from an existing view, copying both data and structure fields?
0
bobdylan75
Asked:
bobdylan75
  • 2
  • 2
2 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:

Simple enough ...
create table MyNewTable as (
  select whatever
    from MyView
)
with data

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Bob,

DB2 generally requires that you create the table and populate it in separate steps.  That's a pretty small limitation though.


  CREATE TABLE mytable AS (SELECT * FROM myview) DEFINITION ONLY;

  INSERT INTO mytable SELECT * FROM myview;

You'll have to modify that slightly if the table has an IDENTITY column, and you'll have to reapply any foreign keys, triggers, or constraints that you need.


Good Luck,
Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Dave.

'with data'.  When did that come into existence?


(pretty cool)

Kent
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Hi Kent,

I’m currently using 6.1 (a.k.a. V6R1) on System i (a.k.a. AS/400). From the SQL Reference manuals, I can see that the "WITH DATA" functionality existed in 5.4 (in 2006) and 5.3 (in 2005), but I don’t see that functionality listed in the 5.1 manual (from 2001).

I no longer have the 5.2 manual, so I can't check that one.

I don't know how that translates to DB2 on other platforms, though.

HTH,
DaveSlash
0
 
bobdylan75Author Commented:
thanks
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now