Replication of Tables
As of November 8, 2021, new Connect users can access the Connect Service using the NetSuite2.com data source only. If you gained access to the Connect Service before this date, you can still access the NetSuite.com data source to ensure a smooth transition to NetSuite2.com.
Note that the NetSuite.com data source is no longer being updated with newly exposed tables and columns, and support for this data source will end in a future release. The use of the NetSuite.com data source is no longer considered a best practice, and all Connect users are encouraged to use the NetSuite2.com data source. For more information about this change, see New Accounts and Access to the Connect Data Source.
The following are some techniques for replicating of large data for when using SELECT * FROM TABLE statement leads to an error.
-
Return columns subset. Use only the necessary subset of return columns. For example, instead of:
SELECT * FROM TRANSACTIONS
use:
SELECT trandate, trandoc, total_amount FROM TRANSACTIONS
Using * column selector is demanding on resources.
-
Slicing. Slice one page result set by a condition in where clause that helps to divide the replicated data into blocks with predicted size. This requires a cycle. For example, instead of:
SELECT * FROM TRANSACTIONS
use:
select * from TRANSACTIONS where transaction_id >0 and transaction_id <= 1000000 select * from TRANSACTIONS where transaction_id >1000000 and transaction_id <= 2000000 select * from TRANSACTIONS where transaction_id 2000000 and transaction_id <= 3000000 ...
-
List of IDs and replicate. Fetch a list of IDs and then replicate by batches then iterate over the returned list and retrieve one transaction at a time. For example, use
SELECT TRANSACTION_ID FROM TRANSACTIONS
then iterate over all TRANSACTION_ID list
SELECT trandate, trandoc, total_amount FROM TRANSACTIONS where TRANSACTION_ID = <xxx>
-
Combination of Return column subset and Slicing. For example,
select trandate, trandoc, total_amount from TRANSACTIONS where transaction_id >0 and transaction_id <= 1000000 select trandate, trandoc, total_amount from TRANSACTIONS where transaction_id >1000000 and transaction_id <= 2000000 select trandate, trandoc, total_amount from TRANSACTIONS where transaction_id >2000000 and transaction_id <= 3000000
-
Combination of Slicing and List of IDs and replicate.
SELECT TRANSACTION_ID FROM TRANSACTIONS SELECT trandate, trandoc, total_amount FROM TRANSACTIONS where TRANSACTION_ID = <xxx>
-
Combination of all techniques.
select TRANSACTION_ID from TRANSACTIONS where transaction_id >0 and transaction_id <= 1000000 select TRANSACTION_ID from TRANSACTIONS where transaction_id >1000000 and transaction_id <= 2000000 select TRANSACTION_ID from TRANSACTIONS where transaction_id >2000000 and transaction_id <= 3000000
then iterate over all TRANSACTION_ID list
SELECT trandate, trandoc, total_amount FROM TRANSACTIONS where TRANSACTION_ID = <xxx>