Can I use a temp table in a DTS package?

[RottPaws]

I have a local DTS package that extracts some data from an Oracle database.

I need to have some additional information extracted, but he query to pull it is a monster. Basically I want to pull circuits that meet certain criteria and are not related to a list of orders that meet other criteria.

I have a query that retrieves the list of orders that I want excluded and I use that as a sub-query in the WHERE statement for the circuit retrieval query.

It looks something like:
SELECT circuit_name, related_order
FROM circuits
WHERE circuit_status = 'Active' and related_order not in (SELECT order_no FROM orders WHERE ...)

The sub-query runs and retrieves a list of orders in about 5 minutes. And the main query runs without the sub-query. If I put the two together it takes hours and then fails.

Is it possible to run the sub query on it's own and store the list of orders in a temporary table and then use that table as the source of the IN clause for the query in the transform workflow object?

Is there maybe a better way to accomplish this?

I'm pretty new to SQL Server, so if you could spell out the solution for me, or give me the key words to search for and read about in books online, that would be big help. I've searched, but come up with nothing that seems relavent to what I'm trying to do. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[dhoward007]

You can use tmp tables in DTS Packages.. the syntax is


just like creating a normal table.. I use tmp tables for lookups like the following assuming you have a table called servers.

e.g. SELECT computer_id, servername INTO #serveridlookup from servers

that is the quick and easy way.. Don't forget to drop the table when you are through..

You just use a pound sign in front of the table name.

Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

 

[RottPaws]

Thanks for the response. My question may not have been clear. My problem is that I need to either use the temp table as a source for the IN operator in the WHERE statement or join the temp table to an Oracle table of another query pulling information from the Oracle database.

To expand on your example, say there's another table that is a log for server errors. The first query finds a list of servers that I don't want to see error information for and puts that list into a temp table.

Now I need to run another query against the errors table to find all errors that are for servers not on the list retrived from the 1st query.

something like:
SELECT *
FROM ServerErrors
WHERE computer_id not in (SELECT computer_id FROM #serveridlookup)

Can I reference a SQL7 table or join it with an Oracle table in the query?

The other possible solution that occurred to me last night was to retrieve the first list into a temp table (list of server ids to exclude in the example above), then retrieve the 2nd list (all errors that meet other criteria regardless of computer_id) into a temp table with all the criteria except the list of orders to be excluded. Then I can run a 3rd query in SQL7 to bump those tables and get the records in the 2nd table that aren't on the exclude list in the 1st table.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[dhoward007]

I have never tried to join an oracle table but I think as long as you can query the table within SQL then you should be able to join it.


about your question.. How about something like .

select all servers that you need to view errors into a temp table then join this table with the error table by computer id or servername. This will give you all errors where the servers match.

 

[RottPaws]

The problem is I can't provide a list of things to match. I've got one database that has orders and another that has circuits related to orders, but there is no referencial integrity between the two.

I can use criteria to narrow down specific circuits I am looking for in the database, but I only want to see it for circuits that have invalid order numbers.

So what I was trying to do was have a query cross-reference both databases to find all the valid order numbers (this works) and then use that list of order numbers as a NOT IN() clause in the WHERE statement to get everything else. But when I use the "valid order numbers" query as a subquery, it fails.

My next hope was that I could save the list to a temporary table and then join that to the Oracle table in my query to pull back the records where there was not a match in the order number.

I am making progress in doing it the way I described above. That is, I'll pull the list of valid order numbers into a table in SQL7 and then pull all the circuits into another table and then bump the 2 together and get whatever falls out. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!