Is there an equivalent @@RowCount (MSSQL) in Vertica, and ways to store their results into Objects (

Roman_1

As i understand next two functions,GET_NUM_REJECTED_ROWS(), GET_NUM_ACCEPTED_ROWS(), work only with COPY command, but i cant added their results into any table like "insert into (table) select GET_NUM_REJECTED_ROWS();" is any way to do that. And what about if i want to store the results after INSERT INTO command (number of loaded counts), is any function to solve this. Could anyone help me with that, Appreciate for any help Thanks

[Deleted User]

You can get the information of the copy command in the table load_streams, there is a column for the rejected and accepted rows. 
Hope this helps, 
Eugenia

Roman_1

Thanks Eugenia, 

Couple notes, when i try to load with COPY not all  succesfull attempts have result string in load_streams, is it OK? or i maybe have some troubles with hardware?

and the second, is there any object like load_streams, which describe similar results to INSERT command

Thanks

[Deleted User]

Yes, you are right, only the one that take more than 1-2 seconds will be there. If you want to get all of them add the PROFILE word before the COPY statement. 

About the insert, there is nothing, however, the insert will fail or rollback, you won't have row rejected and accepted. Not sure if I understand your question? 

Eugenia

Roman_1

PROFILE for COPY that is fantastic, thanks, it solve my question completely.

About INSERT, i mean that if i do simple insert, for example 
insert into ... select ... and i have 50 rows which added into table, and i'll be able to see that 50 rows as result for that transaction in table like load_streams for COPY.

[Deleted User]

if you profile the insert you could do this : 
dbadmin=> profile insert into test select * from test;

NOTICE 4788:  Statement is being profiled

HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273715232 and statement_id=8;

NOTICE 3557:  Initiator memory for query: [on pool general: 109568 KB, minimum: 109568 KB]

NOTICE 5077:  Total memory required by query: [109568 KB]

 OUTPUT 

--------

    466

(1 row)

Time: First fetch (1 row): 3288.437 ms. All rows formatted: 3288.497 ms

dbadmin=> Select sum(counter_value)/2 from v_monitor.execution_engine_profiles where counter_name = 'written rows' and transaction_id=current_trans_id() and statement_id = current_statement()-1;

        ?column?        

------------------------

 466.000000000000000000

(1 row)

Time: First fetch (1 row): 663.605 ms. All rows formatted: 663.638 ms

dbadmin=> 

Hope it helps, 

Eugenia

Roman_1

Yes, that's exactly what i need. 
Lots of Thanks !!!!!

[Deleted User]

My pleasure ! glad that I helped. 
Have a nice halloween! 
Eugenia