This is the final corrected version! Feel free to modify this process!
Hehe.
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 EDWARDS.EMPLOYEE
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
---------------------------------------------------------------------
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