Dynamic Procedure To Create Qvd

[#1]

Emp:
LOAD * INLINE [
    EmpName, Salary
    Ram, 4587
    Shyam, 1500
    John, 5000
    James, 2500
    Jim, 1000
];

Emp2:
LOAD * INLINE [
    EmpId, Office
    1, UAE
    2, Ind
    3, Uk
    5, Us
    4, Pune
];

Emp3:
LOAD * INLINE [
    EmpId, Office
    1, UAE
    2, Ind
    3, Uk
    5, Us
    4, Pune
];

1. Using above three tables create the dynamic procedure to create qvd using single store functionality and also it deletes the tables,
2. what i want i want Create the qvd and delete the tables with single statement and need to use that statement when ever we required to delete or create qvd)

[jaimeaguilar · Accepted solution]

Hi,

try with this:

Set vQVD_Folder =  'some_folder' //put here the directory where you want to store your qvds

//Store QVDs

FOR i = 0 TO NoOfTables() - 1

  LET Table = TableName(i);

  STORE [$(Table)] INTO [$(vQVD_Folder)\$(Table).qvd];

NEXT

//Drop tables

LET j = NoOfTables();

DO WHILE j > 1

  LET TableDrop = TableName(0);

  DROP TABLE [$(TableDrop)];

  LET j = NoOfTables();

LOOP

regards

[jjordaan]

See the below example.

It's a simple one but I hope it helps.

Tables2BeLoaded:

LOAD

  Replace(Replace([SQL Table Name],'.','_'),'/','_') AS SQLTable

  ,Alias

  ,Active

Resident Tables

Where Active = 1;

DROP Table Tables;

LET vRowTables = NoOfRows('Tables2BeLoaded'); // Get the total number of tables:

FOR i = 1 to $(vRowTables) // Loop through every row:

  LET vSQLTable = Peek('SQLTable', $(i)-1, 'Tables2BeLoaded');

  LET vTable = Peek('Alias', $(i)-1, 'Tables2BeLoaded');

TRACE i: $(i);

  [$(vTable)]:

  SQL SELECT *

  FROM "$(vDataSource)".dbo."$(vDB_Company)$$(vSQLTable)";

  STORE $(vTable) into [$(vDir)$(vDataSource)$$(vDB_Company)$$(vTable).qvd] (qvd);

  DROP Table $(vTable);

NEXT i; //Loop on tables:

[#4]

Thanks Jaime..its work..

[#5]

Thanks jeroen For Reply both are working..

[jjordaan]

Hi Dhananjay,

Can you mark Jaime and my post as a correct or helpful answer so other community users can also work with these answers.

Thank you