SQL Extract Issues

Associate
Joined
25 Feb 2007
Posts
903
Location
Midlands
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,
 
Associate
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.
 
Associate
OP
Joined
25 Feb 2007
Posts
903
Location
Midlands
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?
 
Associate
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.
 
Associate
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?
 
Associate
OP
Joined
25 Feb 2007
Posts
903
Location
Midlands
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?
 
Back
Top Bottom