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.
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} |
|
|
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. |
|
|
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. |
|
|
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 liketable1: ID, Name, Amounttable2: ID, Name, Amounttable3: ID, Name, Amount..tableN: ID, Name, Amountyou would combine all of those tables into a new table with fields ofTableNumber, ID, Name, Amountwhere 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 |
|
|
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. |
|
|
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. |
|
|
|
|
|
|
|