Combining Multiple Insert Statements with Calculations Fields

Database

Combining Multiple Insert Statements with Calculations Fields

Hi All

I have a table with 55 fields which I need to populate. Most of the fields can be hard coded in as below with 'NOT APPLICABLE' but mixed in between theses there are fields that will require calculations to be done which are derived from other tables(see below CASE statement as an example). The Issue I am coming across is that I want to populate the fields in the order they are listed but Cannot use the INSERT INTO clause inside the CASE Clause so basically I am struggling on how best to lay this out so that the fields are populated sequentially. I could run individual insert statements like below but ideally would like a solution where I can run one complete statement which populates the whole 55 fields. Hope I have explained this OKAY.

Any help on htis would be very much appreciated

Regards

Donal

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BOR_OCCUPATION_STATUS_CURR = 'NOT_APPLICABLE')

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BOR_OCCUPATION_STATUS_ORIGN = 'NOT_APPLICABLE')

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BOR_AGE_AT_ORIGINATION = 'NOT_APPLICABLE')

TRIM(CASE

INSERT  INTO  DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_ID)

select  CUST_ID

FROM DDEWV50P.CRDM_ENTERPRISE_PERIODIC_FACT

where CUST_ID like  "B_")

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (YEAR_OF_BIRTH = 'NOT_APPLICABLE')

INSERT INTO DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO

VALUES (BORROWER_SEX = 'NOT_APPLICABLE')

6 REPLIES
N/A

Re: Combining Multiple Insert Statements with Calculations Fields

You might need to spend some reasonable time on the SQL basics 

Each of your insert into tab values xxx will create a row - populated only one column. 

Insert / Select statement where you define some constants should do.

Insert into tab

select 'NA',

          'NA',

          CASE when a.x = 1 then 1 else 0 end,

...

form tablea;

Re: Combining Multiple Insert Statements with Calculations Fields

Hi,

I want to run multiple COLLECT STATS statements simultaneously on the Teradata database.

How can i achieve  this using BTEQ script, can i run these multiple statements in BTEQ ?

I tried following way 

collect stats on table column (row_wid)

;collect stats on table column (order_wid);

(Above syntax we can follow for multiple select and insert

Ex:

INSERT INTO X

SELECT * FROM A

;INSERT INTO X

SELECT * FROM B;


This works.

)

but its not working for collect stats, is there any limitations on this syntax?

Is there any other way to achieve for same?

Thanks in adavance.

Re: Combining Multiple Insert Statements with Calculations Fields

No, you can't run COLLECT STATS statements like that in BTEQ. You have do something like:

collect stats on table column (row_wid);

collect stats on table column (order_wid);

N/A

Re: Combining Multiple Insert Statements with Calculations Fields

Hi Mahesh,

you have to split the collects into multiple scripts and then use multiple BTEQs to run them.

Or you have a look at the approach i described in 

http://developer.teradata.com/node/16550

Dieter

Re: Combining Multiple Insert Statements with Calculations Fields

Hi Dieter,

Thanks for your reply,

Already we implemented same way (splitting into multiple scripts) except same is there any another way to run multiple statements through BTEQ.Long back we segregated into multiple scripts (13 Scripts) still it was running for long time (>4hours).

Thanks in advance

Re: Combining Multiple Insert Statements with Calculations Fields

Sorry ! you have already given a link for same.Let me read your article soon we will get back to you.