SQL Extract Issues

[cotton31]

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:

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,

 

[cotton31]

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.

Any ideas?

 

[cotton31]

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?

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?