1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Extract Issues

Discussion in 'HTML, Graphics & Programming' started by cotton31, 10 Mar 2011.

  1. cotton31

    Hitman

    Joined: 25 Feb 2007

    Posts: 887

    Location: Stoke on Trent

    Hi,

    I need to extract data from our data warehouse to import into a new program. The new program requires a csv file which contains a unique ID and a calculated figure. I have to write a query to calculate each value as they aren't stored anywhere in the DB (i.e. I can't just use SELECT field1 FROM table1) so I'm thinking it's going to have quite a performance impact.

    My main problem is how do I link each individual query to it's correct unique ID (the new program has already been populated with these).

    My thought was to do something like this:

    Code:
    SELECT 'Key001' AS UniqueID
    , (SELECT field1 * field2 / field3 FROM table1) AS CalculatedValue
    
    UNION 
    
    SELECT 'Key002' AS UniqueID
    , (SELECT field1 * field2 / field3 FROM table2) AS CalculatedValue 
    The above is very simplified, but in essence I would be embedding a select statement for each one of the UniqueID's, which I'm not sure is very efficient.

    Any ideas? Sorry if I've poorly explained what I need!

    It's SQL Server 2005.

    Cheers,
     
  2. Ciphon

    Wise Guy

    Joined: 9 Dec 2008

    Posts: 2,341

    Location: Hampshire

    So the unique ID's already exist in the new system, but nowhere in the old? How do you identify which calculation goes with which ID?

    If there is a link that you can use from your existing data wharehouse to the new system unique ID's, then I'd pull the data from the new system and insert it into a table.

    You can then perform your query to ge the values you need, then drop the table when you're done with it.
     
  3. cotton31

    Hitman

    Joined: 25 Feb 2007

    Posts: 887

    Location: Stoke on Trent

    That's sort of my issue, no the unique ID's in the new system don't exist anywhere in the data warehouse.

    Obviously I know which calculations go with which ID, but I've got no way of transferring this to the DB/query. :confused:

    Any ideas?
     
  4. Ciphon

    Wise Guy

    Joined: 9 Dec 2008

    Posts: 2,341

    Location: Hampshire

    If there is absolutely no way that you can link the two together, then you're a little stuck!

    Only other suggestions I can think of is to export to excel if you're not dealing with too many rows (max 65k~ Excel 2007, max 100k~ Excel 2010) then you could do some kind of look up.

    There must be some form of logical link if you're able to find an ID and confidently say A and B values go with that ID, so ID = A+B.
     
  5. Yakyb

    Gangster

    Joined: 8 Sep 2009

    Posts: 394

    how many tables are there that you need to query?

    could you not set up a excel list of

    Key , table


    and use that in a console app to generate results?

    performance issue aside (you can always use a backup on a separate machine) would that not solve your issue?
     
  6. cotton31

    Hitman

    Joined: 25 Feb 2007

    Posts: 887

    Location: Stoke on Trent

    That's sort of what I was thinking - I was going to create a temp(ish) table with the id, and then the value pulled by the calculation. Then set up some sort of schedule which ran daily to recalculate the values. My only worry was the efficiency of my query in my OP, would that (embedding selects) be the most efficient way of doing things?