Oh my, Phew! I am tired of those "cast" and "char"! Ouch! hehe. Now,
let me see! I am "just a GUI DBA"! Maybe I can create those "insert"
statements using my favorite GUI (the Control Center)! Hehe.
Ahhhhhhh, yes! Hehe.


1. Open the Control Center
2. Highlight the applicable table, right click it, click on "Export"
3. Next to "Output file", type "C:\EMPLOYEE.txt"
4. Under "File format" select "Delimited"
5. Under "SELECT statement", type the following line
SELECT 'INSERT INTO EDWARDS.EMPLOYEE VALUES(', EDWARDS.EMPLOYEE.*,
');' FROM DEPDB.KM_USER

6. Next to "Message file", type "C:\EMPLOYEE.msg"
7. Click OK


Notes

1. The "C:\EMPLOYEE.txt" and "C:\EMPLOYEE.msg" files must not exist
before doing this process.

2. After running this process, you must edit the "C:\EMPLOYEE.txt" file
with an editor like Notepad, Wordpad, Textpad, etc, changing the
following using (REPLACE ALL):

From "INSERT INTO EDWARDS.EMPLOYEE VALUES("

To: 'INSERT INTO EDWARDS.EMPLOYEE VALUES('

AND

From: ");"

To: ');'


It's almost Fridayyyyyyyy! Partyyyyyyyy! You know what I mean, Vern?
Hehe.



-----Original Message-----
From: Matt Eakle
[mailto:db2udbdba-ezmlmshield-x11628590.[Email address protected]
Sent: Monday, April 23, 2007 10:03 AM
To: LazyDBA Discussion
Subject: RE: insert script thru result set

I think what you're looking for is something like:

select 'insert into tablex (col1, col2) values (' ||
rtrim(cast(arcidentityky as char(16))) || ', ''' || updateuser || ''')
;' from arcidentity

If ARCIdentity has 9 rows, the resultset will be:

------------------------------------------------------------------------
----
insert into tablex (col1, col2) values (1, 'system');
insert into tablex (col1, col2) values (2, 'system');
insert into tablex (col1, col2) values (3, 'system');
insert into tablex (col1, col2) values (4, 'system');
insert into tablex (col1, col2) values (5, 'system');
insert into tablex (col1, col2) values (6, 'system');
insert into tablex (col1, col2) values (7, 'system');
insert into tablex (col1, col2) values (8, 'system');
insert into tablex (col1, col2) values (9, 'system');

9 record(s) selected.

Note that you have to cast numeric data types to a char type in order to
use the concat (||) function. To represent a single quote inside your
output, use three consecutive quotation marks.

Matt

-----Original Message-----
From: Edwards Ed
[mailto:db2udbdba-ezmlmshield-x81397405.[Email address protected]
Sent: Monday, April 23, 2007 5:42 AM
To: LazyDBA Discussion
Subject: RE: insert script thru result set

Nadeem,
Did you figure out how to create insert statements?





Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Khan Nadeem
[mailto:db2udbdba-ezmlmshield-x47964965.[Email address protected]
Sent: Friday, April 20, 2007 1:29 AM
To: LazyDBA Discussion
Subject: insert script thru result set


Hi All,



Is there any way in DB2 to generate Insert scripts from result set



I mean I have result set of abt 100 rows I need to generate insert
scripts



So is it possible



Do leme know





Nadeem



This message contains information that may be privileged or confidential
and is the property of the Capgemini Group. It is intended only for the
person to whom it is addressed. If you are not the intended recipient,
you are not authorized to read, print, retain, copy, disseminate,
distribute, or use this message or any part thereof. If you receive this
message in error, please notify the sender immediately and delete all
copies of this message.


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page