CREATE TABLE - MULTIPLE USERS Extension on table name

[Corinne]

HI,

I'm trying to create a table each time a user accesses information based on a query that pulls selected data from two joined tables in the same database. Ideally I would like this table to be temporary but as multiple users will possibally be doing the same action at the same time, I'm thinking that as they do their individual query that creates the table, that the table could be renamed with something to make it unique (LANID or time created). Would something like this be possible? I'm really not sure what the syntax would look like so as specific as you could be would be helpful. I'm using VB6 & SQL7. The tables that the original query accesses are tblEntry and tblSystemHeaders. The results would be put in a temp table (tblReports). Like I said, I need that table to be unique as multiple users could be creating at the same time. Is this possible? Please help.

Thanks,
Corinne

 

[tlbroadbent]

Each user will have a SQL Server session so using a temporary table will not require appending user name or any type of prefix or suffix.

Create the table with a "#" as the first character of the table name. This makes the table a local temp table accessible only to the current session. Multiple users could create #tblReports at the same time and each would have their own version of the table. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Corinne]

tlbroadbent,

Thank you for responding so quickly. I have an additional question. Can you explain how the system knows which user will access which #tblReports?

Thanks,
Corinne

 

[tlbroadbent]

SQL tracks the temp tables by ID number. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Corinne]

Do I need to worry about emptying out the table at all? Or deleting when the user is done?

Thanks,
Corinne

 

[tlbroadbent]

Temporary tables are created new for each session and are dropped when the session ends. My policy is to Drop tempoary tables when finished with them. Though not necessary, it is good policy to cleanup at the end of a process. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Corinne]

I used you suggestion about putting the x in front of the table name & nothing happened when I ran the code. Can you tell me what I still have wrong please?

strLANTable = "Create Table #tblReport(ID int,AcctNum nvarchar(10),CutOff datetime,Code int,EntryDate smalldatetime, "

strLANTable = strLANTable & "LanID nvarchar(8),CheckNo nvarchar(20),CreditAmt money,CreditDate smalldatetime,DebitAmt money, "

strLANTable = strLANTable & "DebitDate smalldatetime,AmtDiff money,Memo nvarchar(50),Expr1 int,Expr2 int,ExpDet nvarchar(2000), "

strLANTable = strLANTable & "Heading nvarchar(50),lblCheckNo nvarchar(25),lblCreditAmt nvarchar(25),lblCreditDate nvarchar(25), "

strLANTable = strLANTable & "lblDebitAmt nvarchar(25),lblDebitDate nvarchar(25),lblAmtDiff nvarchar(25),lblMemo nvarchar(25))"

cnnREX.Execute strLANTable

Thanks,
Corinne

 

[tlbroadbent]

What x?

What did you expect to happen with that code? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Corinne]

I'm sorry I mistyped I meant to type "#"

Corinne

 

[tlbroadbent]

You didn't reply to the second question. The code you posted would create a temp table. Then you would need to populate the table, select from the table, etc. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Corinne]

I'm sorry not to have responded. I thought that was what I was suppose to do, create a temp table for all of the users and then it would get populated with the results of their query. Here is the code that populates tblReports:

strSQLREX = "SELECT tblEntry.ID, tblEntry.AcctNum, tblEntry.CutOff, tblEntry.Code, tblEntry.EntryDate, tblEntry.LanID, tblEntry.CheckNo, "

strSQLREX = strSQLREX & "tblEntry.CreditAmt, tblEntry.CreditDate, tblEntry.DebitAmt, tblEntry.DebitDate, tblEntry.AmtDiff, "

strSQLREX = strSQLREX & "tblEntry.Memo, tblSystemHeaders.ID AS Expr1, tblSystemHeaders.Code AS Expr2, tblSystemHeaders.ExpDet, "

strSQLREX = strSQLREX & "tblSystemHeaders.Heading, tblSystemHeaders.lblCheckNo, tblSystemHeaders.lblCreditAmt, tblSystemHeaders.lblCreditDate, "

strSQLREX = strSQLREX & "tblSystemHeaders.lblDebitAmt, tblSystemHeaders.lblDebitDate, tblSystemHeaders.lblAmtDiff, "

strSQLREX = strSQLREX & "tblSystemHeaders.lblMemo Into #tblReport "

strSQLREX = strSQLREX & "FROM tblEntry LEFT OUTER JOIN tblSystemHeaders ON tblEntry.Code = tblSystemHeaders.Code "
strSQLREX = strSQLREX & "WHERE (tblEntry.AcctNum = '" & Trim(txtAcctNum) & "') AND "

strSQLREX = strSQLREX & "(tblEntry.CutOff = '" & Trim(cboCutOff) & "')"

 

[tlbroadbent]

The table is created in the SELECT INTO statement. The table must be created each time a session is started. You cannot create a temp table one time and let everyone use it. It wouldn't be temporary in that case.

What happens after #tblReports is created?

I recommend that you create a stored proceure that performs the insert into the temp table, does whatever other manipulation is required, returns a result and drops the temp table. You VB program would then execute the stored procedure, passing necessary parameters and receive the result set. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Corinne]

I'm sorry to be difficult, but I don't understand. The code I posted to create #tblReports, is that correct? I'm guessing that the second portion of code I posted is the part that is incorrect but I'm not following you on all of the pieces I need to make this happen. I've ever written a stored procedure before so I'm even unsure of how to do that.

Thanks, Corinne

 

[tlbroadbent]

The code you posted to create the temp table is not necessary. It appears to be correct but you don't need to run it. The other code you posted is a SELECT INTO query. SELECT INTO creates the table. That is why the CREATE TABLE query is not needed.

You don't show any code that uses the table after inserting recods. I assume there is additional code that selects from the temp table.

Let me know if you have aditional questions. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Corinne]

Terry,

Thanks for responding. Yes, there is additional code that puts this daat into a report. My report is receiving the data through the data environment. I created a command and grouped the data as needed. I think I now have all of the pieces to do this create table. It was just that I was trying to make sure that when the table gets created that the data is moved to the report before the next user goes in and requeries for different criteria. Thanks for all of your help.

Corinne