Insert select in Mload

Tools
Enthusiast

Insert select in Mload

Hi,

I have a situation in which i have to insert into a table from another table. The size of the table is very huge (3+TB). Can i write a statement :

Insert into B

sel * from A

into mload and run.

I want to use mload as this will not have the problem of rollback if any issues in insert.

If someone has the code for this , then please share. It's really urgent.

15 REPLIES
Supporter

Re: Insert select in Mload

Is table B empty?

Junior Contributor

Re: Insert select in Mload

MLoad Inserts are row by rown no Insert/select.

And when you use Insert/Select outside of BEGIN/END MLOAD (= within Support Environment) it's like any SQL usiing the Transient Journal. That's why Ulrich asked if the table is empty, then it would be a Fast Path Insert Select without journaling.

When you expect errors you might also try to create an Error Table for the target and Insert/Select using the LOGGING ERRORS option to avoid a rollback.

Dieter

Enthusiast

Re: Insert select in Mload

Yes the table B is empty.

Enthusiast

Re: Insert select in Mload

Hi dnoeth,

Could you explain you answer in detaiil.

We are planning to use Mload as the table is very big. Mload would avoid journaling and in case aborted, the rollback would not have to happen.

Could you please share the code snippet for this.

I read through the available material, and all say that select is not allowed in the mload. So, i am little confused,

Enthusiast

Re: Insert select in Mload

I tried the way you suggested..i am getting the below eror

#!/bin/ksh

mload <<EOF

.Logtable SAM4;

.LOGON id/pass;

.BEGIN  MLOAD

Tables xx.TEST2 - my target table

WORKTABLES yy

ERRORTABLES zz bb;

;

.END MLOAD;

.CREATE ERROR TABLE TRAN_ERR FOR test2;

insert into test2

sel * from test;

.Logoff;

.exit

EOF

Error:

===

**** 18:17:26 UTY0830 Attempting to restart this MultiLoad import task from the application

     phase.

**** 18:17:26 UTY0831 This MultiLoad import task was newly started or in the preliminary phase

     and not in the application phase as expected, terminating.

Teradata Employee

Re: Insert select in Mload

General statement:

SQL statements cannot start with a period (".").

So, the CREATE ERROR TABLE statement should not have the period.

However, you are trying to use MultiLoad in a way that is not intended.

If you want to issue an INSERT-SELECT, use BTEQ.

Your MultiLoad script has no .IMPORT statement to run the acquisition phase of the MultiLoad job.

-- SteveF
Enthusiast

Re: Insert select in Mload

I tied this code as suggested ny Dnoeath. He suggested to do an insert - selct after the .end mload commad (Please refer to my initial question: i inted to use an insert-select in Mload)

Teradata Employee

Re: Insert select in Mload

I have read all of the posts here and I will repeat: you are trying to use MultiLoad in a way that is not proper.

MultiLoad is a load tool to provide the capability of performing insert, updates and deletes for 1-5 tables.

MultiLoad is expecting the data to come from flat files, INMODs, or Access Modules.

It is not intended for performing actions such as INS-SEL.

If you would like to use MultiLoad to load data from, say, a flat file, into a staging table first (and that type of task should really be performed by FastLoad), and then follow that up with an INSERT-SELECT, that might be possible (never tried it).

But for what you are trying to do, MultiLoad is not the correct tool.

You may want to "intend to use an insert-select in MLoad", but I am not sure why and that is not what MultiLoad is for.

-- SteveF
Enthusiast

Re: Insert select in Mload

Hi feinholz,

I totally agree with you.

However, i was told by my lead that pls use Mload for insert-select , which i am sure is not the right use of mload, but i am trying to know if that is really possible (even if in some wierd way!!) . Thing is that i can't go and ask my lead for the script and can't say no unless i am 100%  sure of it !! Hope you understand