Skip to main content

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

  • October 31, 2013
  • 7 replies
  • 22 views
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

    7 replies

    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • October 31, 2013
    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
    • Roman_1
    • Author
    • New Participant
    • October 31, 2013
    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
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • October 31, 2013
    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
    • Roman_1
    • Author
    • New Participant
    • October 31, 2013
    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.
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • October 31, 2013
    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
    • Roman_1
    • Author
    • New Participant
    • October 31, 2013
    Yes, that's exactly what i need. 
    Lots of Thanks !!!!!
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • October 31, 2013
    My pleasure ! glad that I helped. 
    Have a nice halloween! 
    Eugenia
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement