View previous topic :: View next topic
|
Author |
Message |
rahuindo
New User
Joined: 09 Apr 2008 Posts: 83 Location: Chennai
|
|
|
|
When i am trying to generate bulk insert query using SELECT statement (for few specified columns) from a table, the query is not working if the columns have the following datatypes
1. DATE
2. NUMBER
3. packed decimal
Ex:
SELECT 'INSERT INTO <tb1> VALUES(' || <col1> || ',' ||
col2
|| ')'|| ';'
FROM <tab1> WHERE TIN_NUM = <Tin num>
The col2 column in the above query has packed decimal datatype. |
|
Back to top |
|
|
anandinmainframe
Active User
Joined: 31 May 2007 Posts: 171 Location: India
|
|
|
|
Rahuindo,
Please post(ERRORS or SQLCODES) when you try to insert. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi rahuindo,
TRY something like this,
Code: |
SELECT 'INSERT INTO <tb1> VALUES('
|| <col1>
|| ','
|| CHAR(col2)
|| ')'
|| ';'
FROM <tab1>
WHERE TIN_NUM = <Tin num> |
If you get an error, post the ERROR along with column definations.
Thank You,
Sushanth |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
It would be a good idea to provide your sql, not the paraphrased in your first post as well as column definitions from ddl. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hello,
My Table details
Code: |
Table : Country
ID INTEGER
NAME CHAR
DESCRIPTION CHAR |
I tried like this one
Code: |
SELECT 'INSERT INTO BOBT1.COUNTRY VALUES('
||CHAR(ID +1)
||');'
FROM BOBT1.COUNTRY; |
Resulted like
Code: |
INSERT INTO BOBT1.COUNTRY VALUES(2 );
INSERT INTO BOBT1.COUNTRY VALUES(4 );
INSERT INTO BOBT1.COUNTRY VALUES(5 );
INSERT INTO BOBT1.COUNTRY VALUES(6 );
INSERT INTO BOBT1.COUNTRY VALUES(102 ); |
Thank You,
Sushanth |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Sorry Sushanth,
I posted while you were posting.
my comment was directed to rahuindo.
with luck, you have answered rahuindo's question. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Happy to help DINO |
|
Back to top |
|
|
rahuindo
New User
Joined: 09 Apr 2008 Posts: 83 Location: Chennai
|
|
|
|
Thanks to all your advice. The query worked with charm as provided with Susanth.
As adviced, i will write the complete query from next time. Thanks!! |
|
Back to top |
|
|
|