How do I select data from row 1000000 to row 200000 from a table

[Former Member]

I need to load a table using insert location the source table has got 40,000,000 row, my insert location does not work as my target server has not enough memory configured. I would like there to load the table by packages of 5,000,000 rows. How can this be acheived?

[phil_soady]

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_up_to_offset.htm

[Former Member]

Friends, none of these helped me, but I used your proposition to resolve my problem, duplicated the  table added the rowid values to the new table and then loaded the new table using rowid values by increments of 5,000.000. I wish we had the row number integrated into the Sybase in teh same way as db2 or oracle.

[markmumy]

What do you mean by "Sybase"?  ASE?  IQ?  SQL Anywhere?

Mark

[Former Member]

Hi Mark,

Sorry for the imprecision, I meant I wish we has the possibility of saying where rowid between for example 5,000,000 and 15,000,000. I wish we had the possibility both in ASE and IQ.

I would like to appreciate everybody's help in clarifying the rowid use in IQ to me with your hint I could resolve my problem without wasting much time. My effort to use rowid between x and y could not fruitful.

[former_member188490]

Hi,

  >>  I would like there to load the table by packages of 5,000,000 rows. How can this be acheived?

  You can try using insert-load options.

  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1600/doc/html/san12...

  insert-load-options –
    [ LIMIT number-of-rows ]
    [ NOTIFY number-of-rows ]
    [ SKIP number-of-rows ]

  For eg. I want to load my destination table using 5 rows at a time. I will try this.

Thanks & regards
Milind ..

(DBA)> insert TmpEmployees limit 5 location 'iqdemo_IQ16SP08PL20_N0.iqdemo' { select * from GROUPO.Employees }
5 row(s) inserted
Execution time: 0.04 seconds

(DBA)> select EmployeeID from TmpEmployees
EmployeeID
-----------
        102
        105
        129
        148
        160

(5 rows)

Execution time: 0.003 seconds

(DBA)> insert TmpEmployees skip 5 limit 5 location 'iqdemo_IQ16SP08PL20_N0.iqdemo' { select * from GROUPO.Employees }
5 row(s) inserted
Execution time: 0.05 seconds

(DBA)> select EmployeeID from TmpEmployees
EmployeeID
-----------
        102
        105
        129
        148
        160
        184
        191
        195
        207
        243

(10 rows)

Execution time: 0.003 seconds

[markmumy]

What is the source database?  If it is IQ, you could try using the ROWID() function,  though that has some severe limitations and gaps.  If it is another technology like ASE or Oracle, you would need to use a SQL statement that can be sent to the remote system and parsed properly.

Mark

[Former Member]

used the rowid(), it did not work in my case:

select * from mytable where ROWID()<10

version of IQ:

@@version

'Sybase IQ/15.4.0.3014/120310/P/ESD 1/Sun_x64/OS 5.10/64bit/2012-03-10 11:23:59'

[Former Member]

Could not execute statement.

  SQL Anywhere Error -154: Wrong number of parameters to function 'ROWID'

  Sybase error code=174, SQLState="37505"

[Former Member]

The syntax is like:

select mytable.mykeycolumn, rowid("mytable") from mytable where mytable.mkyeycolumn < 10;

[saroj_bagai]

SELECT ROWID( “PRODUCTS” ) FROM PRODUCTS

[saroj_bagai]

You can use rowid()