Populating DB2 Table from SQL Script

Question

I've written simple SQL scripts before, but I'm no expert, so I'm not even sure that what I'm about to ask can be done. From a DB2 SQL script, I'd like to populate a table from the contents of a different table. Eventually, I am going to be bringing in data from other tables, but I just need to get the basics working first. I'm trying to do this from the DB2 CLP, executing a file.

From looking at manuals, I thought this might possibly work:

BEGIN ATOMIC
    FOR ROW AS
        SELECT OSN.SWOSNAME, OSN.SWPLATFORM FROM SASH1.LISTOSNAMES OSN
    DO
        INSERT INTO SASH1.VALIDOSES
            VALUES (ROW.OSN.SWOSNAME, 'IBM', ROW.OSN.SWOSNAME, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Y', ROW.OSN.SWPLATFORM, NULL);
    END FOR;
END

However, I'm getting the following error:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"SN.SWPLATFORM, NULL)".  Expected tokens may include:  "<delim_semicolon>".
LINE NUMBER=6.  SQLSTATE=42601

It's fairly obvious that I don't know what I'm doing here. Is it possible to do something like this (populating a table from data in another table), without having to create SQL Procedures or Functions in the database?

Lennart - Slava Ukraini · Accepted Answer · 2014-07-15 20:06:02Z

1

You are complicating things, all you need to do is to insert the result of the select, make sure you specify the column names (I used col1, ...)

INSERT INTO SASH1.VALIDOSES
    (col1, col2, ... ,coln)
SELECT SWOSNAME, 'IBM', SWOSNAME, 'Y', SWPLATFORM 
FROM SASH1.LISTOSNAMES

Note that you probably don't have to specify NULL (it is default, unless there is a default value specified for the column that you would like to override with NULL)

answered Jul 15, 2014 at 20:06

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Westy Over a year ago

Thanks a bunch, Lennart! I knew I was missing something, this is very easy.

Lennart - Slava Ukraini Over a year ago

Just mark my answer as a solution and we are even ;-)

mustaccio · Accepted Answer · 2014-07-15 20:09:36Z

0

Despite what @Lennart said about overcomplicating things, if you insist on using an anonymous block realize that the entire block is a single SQL statement and as such must have a terminator character at the end. Since you also must have the default statement terminators, the semicolons, within the block, the block itself must use some other character as the terminator. "@" is commonly used, so you would put the "@" character after the BEGIN ATOMIC ... END statement and execute the file:

db2 -td@ -f <yourfile>

answered Jul 15, 2014 at 20:09

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

1 Comment

Westy Over a year ago

Aha! This explains much. Thanks, Mustaccio! Even though I will use Lennart's suggestion, your reply will be useful if I do need to use blocks in the future.

Collectives™ on Stack Overflow

Populating DB2 Table from SQL Script

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related