Solved

to create a temporary table with unique name in SQL 7.0

Posted on 2002-07-10
11
276 Views
Last Modified: 2008-03-10
Dear advisor  !

I use SQL 7.0

I want to create a temporary table for inputing data.  Like that

On computer 1 : The user runs the application

Create Table C:\Temp\OrderDetail
Insert, Delete, Update


On computer 2 : The user runs the application

Create Table C:\Temp\OrderDetail
Insert, Delete, Update

The name of table is not changed. But that will be conflicted.
Are  there any "folder" on SQL Server to  avoid this error.

Before i used Foxpro. it is ok. But i dont know how to do like that in SQL Sever

Thanks for all consider

Beginner
0
Comment
Question by:NamCit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 20 total points
ID: 7142659
in SQL Server you create temporary tables like this:

create table #temp ...

This temporary table (the prefix # is the key) will be available to the connection that created the table, other connections can create a temporary table with the same name without conflicting.

You can try this out by opening 2 query analyser windows and executing the same create table #temptable statement, and then select the tempdb.dbo.objects where name like '#%', you will see that SQL Server uses some internal technique to avoid the conflicts.

Cheers
0
 

Author Comment

by:NamCit
ID: 7142673

Yes , it is ok. But i use application (Delpi 5.0) to connect that Table . I could not see that Table ???

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7142682
>I could not see that Table ???
You cannot see the table unless you create it, but even then it will only be seen by the connection that creates the table.
If you create the table in the query analyser, you obviously cannot see it by delphi application.

Maybe you expand a little bit more what exactly you need, how the table is used, who should create the table, when the table is created, when the table is dropped, will all applications (from all computers) share the same table etc

CHeers
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:NamCit
ID: 7142683

Yes , it is ok. But i use application (Delpi 5.0) to connect that Table . I could not see that Table ???

0
 

Author Comment

by:NamCit
ID: 7142694


Dear angelIII !


I use Delphi5.0 + SQL 7.0

When the user runs my application, The application creates a temporary table on SQL SerVer with a constant name. I use this temporary table for Inser , Update , Delete.

I could not use SQL statement to retrieve Data. Because  If i use SQL to retrieve Data , when i update data , it updates on Server automaticly. I only want to update when i puch the button

I have just used component to connect that table . But it is error. Because the name of table is too long


Hearing your comment




 


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7142703
In SQL Server, you don't use file names for the table names like "C:\Temp\OrderDetail", but simply "tmpOrderDetails". For true temporary table, use # as prefix.
0
 

Author Comment

by:NamCit
ID: 7142711

whether SQL SerVer could create table with constant name for each users who use my appliction.

Before , i used Dbf in FoxPro. When the user runs my application. i create Table on C:\Temp folder.

0
 

Author Comment

by:NamCit
ID: 7142717


IF i do like your comment . I am afraid that the user does not have their own table. When the first user use temptable to modify(Inser , Delete , update) the data , the second user will be effected. Because they use the same table.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7142719
So you want that each user has it's own temporary table, available all the time, while the application can create it if necessary?
The problem is that temporary tables will only last the duration of the connection, while other tables will all need to have different names.
So the only solution i can see is that you have different names of the tables.
CHeers
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7142728
sorry, ignore that comment (didn't see your last comment)

In case all the users will use the same table, but the table shall be "temporary", create the table in the tempdb:
create table tempdb.dbo.OrderDetails (...)

Now, this table will be removed when SQL Server restarts, but if i understood correctly, your application will check if the table exists and create it as necessary.

CHeers

0
 

Author Comment

by:NamCit
ID: 7145150

I could do that ?

If the second user uses the application. It will be conflicted of Table. Maybe, i should create a ranDom name of Table. And query that table with a constant name

SQL can not dont like my above question ?
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question