Skip to content

Refresh table

January 10, 2013

In the post “Remote Server Sybase IQ to Sybase IQ” I said that is common practice for the BI development teams to request test environment data refreshment from production environment.

Here I provide the code to refresh a single table. Even if this is not the most performing loading process, it is easy to implement, works well for small and medium size tables (up to a few millions rows), allow the refreshment of large objects (LOB), and allow delegation of the task to the developers (I’m not concern with data access restriction in this post, probably later I’ll write on how address that issue by encapsulating this process in a store procedure).

Here is the code:

truncate table DBA.MyTable

insert into DBA.MyTable 
LOCATION ‘MyProdIqSrvr.prodDwIqDb’
‘ SELECT * FROM DBA.MyTable’

GO

We truncate the table before inserting the new rows from the production environment using “LOCATION”. For this example, I’m assuming the tables has the same definition in both environments. If you have mismatch tables definitions, you can use the (column_name, column_name …) part of the insert instruction to insert only a subset of the columns and modify the select part to only select the corresponding columns. If you want to filter the rows to insert, you can add a predicate to the select part of the instruction.

Advertisements

From → SAP IQ

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: