"Show Me the Data"

    You can't handle the Data.




    You've been asked to quickly build a prototype of data from a system that has hundreds of tables. You need to extract all of the data from all of the tables and store it into QVD files. Ouch that seems like a lot of manual typing.


    Frankly I'm to lazy for that kind of work. So go ahead and ask me how I would approach that task?


    I would take advantage of the tools that I have like SQL Server or Oracle and let them do what they do best? Maintain the lists of all of the tables they hold and run SQL (TSQL or PL/SQL) statements to show me those values. For SQL Server you can do something pretty simple like this to get a list of all of the tables in the database you are connected to:


    Select * from Sys.Tables.


    Big deal you say what does that have to do with your task of building a massive layer of QVD's. Well that's just the starting point. Imagine that the first table name it shows you is Patients. In Qlik we would need the following code in order to pull the data back, create a QVD and then remove the table.



    SQL select * from Patients with (nolock);

    STORE Patients INTO Patients.qvd (qvd);

    DROP TABLE Patients;


    In case you never thought about it as I do ... what I see is blah-blah-blah boiler plate text "table name" blah-blah-blah "table name" yadda-yadda-yadda table name. So if most of the script to build basic layer 1 QVD's is just boiler plate then let's just wrap our query in SQL Server or Oracle with the boiler plate text.


    select name + ':' + char(13) + char(10) + 'SQL select * from ' + name + ' with (nolock);' + char(13) + char(10) + 'STORE ' + name + ' INTO ' + name + '.qvd (qvd);' + char(13) + char(10) + 'DROP TABLE ' + name + ';' + char(13) + char(10) + char(13) + char(10)

    from sys.tables


    Crazy simple right. If I have 10 tables it builds out the syntax for me to create 10 QVD's. But if I have 100 tables it builds out the syntax for me to create 100 QVD's. Or a 1,000. The wonderful thing is the more tables in the database the more time I save myself.


    If you are using SSMS (SQL Server Management Studio) you can simply tell it to display the results as TEXT instead of in Grid mode and then copy the results and paste it into Qlik.



    "Show Me the Data!" No problem and while the script is running for all 1,273 tables I'll be enjoying some chocolate listening to the birds chirp.


    **** Bonus like from me if you use Oracle and will paste the Oracle syntax to do the same thing here for others to take advantage of.

    **** Bonus like from me if you build the second command ... you know the one that will actually do the LOAD statement from the QVD along with a preceeding load of the fields names so that we can paste it into our layer 2 QVD builder or our application. HINT - You the field names are stored in sys.columns but you knew that and I won't be mad if you use a cursor. Something like below so that we can comment out any fields we don't want, or rename them:



    LOAD PatientID,



         Last_Name ....

    FROM Patients.QVD (QVD);