Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 #Temp Table

Author  Topic 

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-27 : 07:06:00
Hi,

I am using common structure of two fields in more than one table.
for eg: create table #abc (id int,name varchar(10))

i am using the same structure in more than 10 tables.
for eg:

create table #abc1 (id int,name varchar(10))
create table #abc2 (id int,name varchar(10))
create table #abc3 (id int,name varchar(10))
.
.
.
.
.
.
create table #abc10 (id int,name varchar(10))

and so on... this is my requirement. So instead of creating so many temp tables can i use this temp table as a Class.

eg: #abc1 #abc something like this...

Please suggest me whether to continue with so many temp tables are is there any feature to copy the sturcture or using as a class...


Md Anwer Ali




Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-27 : 07:27:42
quote:
i use this temp table as a Class.

Say what? Class? A table in a RDBMSs can't be a class. It does not define variables or methods ...

You probably could use just one temp table. You probably don't even need the temp tables. But how do you suggest we make a suggestion to you without any details about what you are trying to do?

Jay White
{0}
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-11-27 : 08:00:36
I think you are going too deep with your thinking here.....
what's your main (percieved) problem with TEMP tables....too many? too many definitions, too many similiar structures in VSS, too many to backup?



There shouldn't be a problem with defining loads of "Temp" tables....maybe you're expecting them to behave/be managed too much like permanent tables.


The scope/lifeline of TEMP tables is quite limited....they get created, used, finished with and die....and then the process repeats itself....when required. They're visibility can be limited to their immediate calling routine (or be made available/known to tother routines by making them ##temp tables (instead of the standard #temp))


As their scope can be limited....this enables you to have 10, 50, 5000, etc TEMP tables ALL named #abc at the same time....each one is independent of the rest...(they don't even have to have the EXACT SAME structure)....this should/would remove the necessity to name each differently....no more #abc1, #abc2, #abc3, etc....now you just have 3 instances of #abc....and your program/SP just references #abc...as if it was the only TEMP table in the entire world....



if your problem is different from the above....please elaborate more.

Go to Top of Page

sherrer

64 Posts

Posted - 2002-11-27 : 16:44:02
Why is the creation of all these duplicate temp tables a requirement?

Why not one table with and id field to split it up into your groups?

I agree with the others, I think we need some more details on what you are trying to accomplish.

Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 17:18:19
Sherrer hit it right on the nose. If you have a lot of tables, all with the exact same structure, then put them all into 1 table with an added "key" field.

So, if you have N tables with a primary key of ID and 2 other fields like

table1: ID, Name, Amount
table2: ID, Name, Amount
table3: ID, Name, Amount
..
tableN: ID, Name, Amount

you would combine all of those tables into a new table with fields of

TableNumber, ID, Name, Amount

where the new primary key is TableNumber and ID. Then, you can have unlimited "tables" all stored in one!

And you can create or remove these virtual tables just by doing DELETE or INSERT querys.


- Jeff
Go to Top of Page

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-28 : 00:19:26
Hi Everybody,

Thanks for the reply, my actuall problem is i am writing a procedure, which is already there in Oracle.

eg:
1. TYPE quesmrks IS RECORD (quesid number(8),quesmarks number(2));
2. TYPE fques IS TABLE OF quesmrks INDEX BY BINARY_INTEGER;
3. tfques fques;

1. In point 1 i am defining a Structure of Two fields.
2. In point 2 i am creating a table with the the above structure. in this way i can create 'n' number of tables with the same structure.
3. In point 3 i am Creating object with the Point 2 table.

In this way i can have unlimited objects. And all these are temperory objects and exists till the procedure is running.

As u said send in detail so i did.

Now need suggestion.



Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-28 : 08:19:45
quote:
Now need suggestion.
Follow Jeff's and sherrer's advice: one table with an extra column to differentiate each table "group" or "object". This is infinitely more efficient that having multiple identical tables.

If you are going to use SQL Server, you need to learn how SQL Server works, there is a great deal about Oracle that simply does not translate to SQL Server. Trying to get SQL Server to do the same thing Oracle does will only frustrate you.

Go to Top of Page
   

- Advertisement -