Posted: 7/21/2005 6:13:27 AM EDT
|
When writing a query in oracle, you can replace a table name in the from clause with another query, e.g.: Select * From (select * from table where rownum < 100) where rownum < 50 ; Do you know if you can do the same sort of thing in Sql Server 2000? If I could, I'd just create a temporary table and select from that, but the retarded DBA refuses to give us create table abilities. |
|
You can do what I often do -- write dynamic SQL statements. Such as: declare @sql varchar(500) declare @tablename varchar(50) select @tablename ='TestTable' select @sql = 'select * from ' + @tablename exec(@sql) It won't have the speediest performance in the world, but you can build any sort of query you want that way. BTW, been there, done that myself with stupid DBAs. "Here, put these handcuffs on and go to work." |
|
Strange, here's the code I'm trying to run just as an example of doing this: ---------------------------------------------------- select * from (select * from TABLE where case between 100 and 200) where case = 150 --------------------------------------------------- I get back the following message: Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'where'. Any ideas? |
|
SQL 2K has an undocumented stored proc called 'sp_msforeachtable' which might work for you. www.databasejournal.com/features/mssql/article.php/3441031 sp_msforeachtable 'select * from ?' The question mark is a placeholder for the table name. If you need more granularity, you'd need to look into the sysobjects table, a cursor and some dynamically built and executed SQL statements. |
SQL also has table variables which should get you around that. For example...
|
i it possible to get around having to provide the column names and data types for "@MyTempTable"? |
If you can go that far, just do: SELECT * INTO #TempTable. That's a memory only temporary table that lives as long as the connection/transaction does. As for the error you're getting, I believe you have to declare the table as an alias. eg: SELECT * FROM (SELECT * FROM TABLE) AS TABLE1 WHERE CASE BETWEEN 100 AND 150 |
you're right on the error, that's what it was. You don't need to provide an alias to do the same thing in oracle. |
Tried it and that doesn't work.... select * from (select name from sysobjects where type = 'U') as MyTable Theoretically, that should select * from all tables based on the user-defined tables in the database, but it just returns me a list of tables. I just don't think it's possibly to use table aliases from queries. |
cool, I can do that. What happens if the query you're using to fill #temptable returns more data than can be stored in memory? |
no, it works just fine. What you did is (select name from sysobjects where type = 'U') as MyTable so you'd be getting a list of names and calling it mytable then you'd be selecting * from mytable, which would return the list of names. |
My first thought is that your first select doesn't know what "case" is, so it barfs on the "where case = 150" part. Does the "main" query know the column names in the table used in the "inner" query? I wouldn't think that data would be within the scope of the main query. |
|
Ah, I get you now! Which begs the question, why do you need the subquery? Select * From (select * from table where rownum < 100) as Table1 where rownum < 50 Wouldn't this do the same? select * from table where rownum < 50 Curious to know cause I've never needed to do something like that, but it may come in handy in the future.  |
I wouldn't need it in that example. I was just making something up to illustrate what I was trying to do. For the real version, the query I put in the "from" clause will be a bit more complicated. |
I believe the table is still server side so you're only limited by the amount of memory allocated to MSSQL (if any) or ultimately the server's memory limit. If you're processing THAT many rows by this thing, your DBA needs to pull the stick out of his ass and figure a better way to do all this. Also realize that the #temptable (the # is what makes it the temp table BTW, if you use ## it makes it global temporary) can be filled with any SELECT including JOINS so you're not limited by a single table, it will fill with the entire results set and will take on the column defs of the selected table(s). If you need to bounce an idea around just IM me. |
# temp tables are stored in the tempdb database so you're only limited by how large that database can grow. However, like Benny said, if the temp table is going to grow the temdb too large, a different solution maybe be in order. |
D'oh, I forgot about tempdb. Good call. Yes, that's your limit. And if that fills up you've got bigger problems than a DBA with a superiority complex. |
|
I think I follow what you are doing: Here's an example of what I think will do it for you NOTE: Imaginary tables and such, find all employees that have had diciplinary action. Table schema: Users: L_Name, F_Name, Employee_id Discipline: Employee_id, Action, Description Select L_name, F_name, Employee_id where Employee_id in (Select Employee_id from Discipline) If that's not what you're looking for, lemme know. If you are looking for a particluar ROW number, search Help for the FETCH command. |
I think this woman's philosophy is to minimize the amount of work she or her staff could possibly be asked to do ... as opposed to making things work as well as possible for her customers. The #table temp table thing is working pretty well. I put a shitload of records in there amounting to about 4 gigs of data and querying from it seemed to work well. |