Newbie question Local Temp Tables vs Global Temp Tables

I'm trying to find the distinction between Local temp tables vs. Global Temp
Tables.
Seem that Global temp tables have greater persistence when using a stored
procedure with a returning select statement.
Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible
from other network users? i.e. can user using the same stored procedures at
the same time overwrite either of these tables?
Thanks for the answers.
Stephen K. MiyasatoLocal temp tables are private to the connection created the tamp table. Glob
al temp tables are not,
they are ... global.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
> I'm trying to find the distinction between Local temp tables vs. Global Te
mp Tables.
> Seem that Global temp tables have greater persistence when using a stored
procedure with a
> returning select statement.
> Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible f
rom other network users?
> i.e. can user using the same stored procedures at the same time overwrite
either of these tables?
> Thanks for the answers.
> Stephen K. Miyasato
>|||Yes, global temp tables have greater persistence, but I've never found an
actual need to retain a temporary table beyond the context of the procedure
that created it. Have you?
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
> I'm trying to find the distinction between Local temp tables vs. Global
> Temp Tables.
> Seem that Global temp tables have greater persistence when using a stored
> procedure with a returning select statement.
> Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible
> from other network users? i.e. can user using the same stored procedures
> at the same time overwrite either of these tables?
> Thanks for the answers.
> Stephen K. Miyasato
>|||I guess when I did do a stored procedure, I found that the #tempLocalTable
was not available, so I thought using he global tables would have been a
solution. Perhaps I'm doing it wrong. I ended up using regular tables but
when the stored procedure were used on different stations simultaneously, I
would get results not related to the patient.
Thanks,
Stephen K. Miyasato
"JT" <someone@.microsoft.com> wrote in message
news:ugUTNx3TGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Yes, global temp tables have greater persistence, but I've never found an
> actual need to retain a temporary table beyond the context of the
> procedure that created it. Have you?
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
>|||Global and permanent tables are visible to all connections so you need to
account for multi-user environments when using these for transitory data.
It's best to stick with a local temp table or table variable in those cases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OiWhVq4TGHA.1160@.TK2MSFTNGP09.phx.gbl...
>I guess when I did do a stored procedure, I found that the #tempLocalTable
>was not available, so I thought using he global tables would have been a
>solution. Perhaps I'm doing it wrong. I ended up using regular tables but
>when the stored procedure were used on different stations simultaneously, I
>would get results not related to the patient.
> Thanks,
> Stephen K. Miyasato
> "JT" <someone@.microsoft.com> wrote in message
> news:ugUTNx3TGHA.1868@.TK2MSFTNGP09.phx.gbl...
>|||>> BUT, sometimes the CTE doesn't behave as expected, and it does the same l
ookup several times. <<
That is T-SQL; the DB2 implementation is much better and seems to know
when to materalize and when expand a CTE in line.
My real gripe is that T-SQL keeps only one execution plan for a
procedure. Other products keep several plans, look at the parameter
values and pick the plan that is best for that set of values. Thus, if
sex is one parameter for a query againt a Marine personnel data base,
and I pass in "male", I get the tabel scan plan, but if I pass in
"female' I get a plan with an index. I vaguely remember that DB2 can
have 16 plans per proc, but I might be wrong.

newbie how to create temp table and populate

Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation.
I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices.
Basically, a shopping cart!
I thought of using session variables and string manipulations but I am more comfortable with DB queries.
a simple 4 column table would cover everything.
SQL server and VBScript
Thanks
Mhttp://www.sqlteam.com/item.asp?ItemID=2029|||thanks, that was the first one I found on Google, but it just confused me.

just after I posted my Query I looked again on Google and found this:
http://www.programmers-corner.com/article/76 which seemed to be more my level!
I am currently having a go.|||;-) well Google is your friend.|||aye,
Here you go Newbies, the three statement to create a temp table, insert some values and retrieve them:
CREATE TABLE #myTempTable
(
DummyField1 INT,
DummyField2 VARCHAR(20)
)
--------
INSERT into #myTempTable (DummyField1, DummyField2) VALUES (1,2)
--------
SELECT * from #myTempTable

I new it wasn't rocket science, sometimes just writing to a forum clears your mind...enought to formulate the correct query for Google! doh

Newbie Help

Looking to create a flat file from a stored proc that is comma delim.. can
this be done? I know I can use DTS but the table is a temp table and that
wont work.
Thanks for your help in advance.
--
Austin Henderson <><
Network AdministratorYou can shell out to bcp via xp_cmdshell but will have to use a global temp
table (##) as bcp will create it's own connection and your local temp table
would not be visible to it.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
news:uQpnrkbcDHA.384@.TK2MSFTNGP12.phx.gbl...
Looking to create a flat file from a stored proc that is comma delim.. can
this be done? I know I can use DTS but the table is a temp table and that
wont work.
Thanks for your help in advance.
--
Austin Henderson <><
Network Administrator

Newbie : Need Help in joining Multiple tables

I am using a query to get data about temp job & temp rates for an employee database. Problem is this query pulls up two records with different rates for the same work period. THE JOB_RATE table has two different job rates with different JOBRATE_EFFECTIVE_RATE . What candition should I add in this query so that it pulls up the Jobrate applicable to that particular WORKDATE & not all JOBRATES .

i.e,
say if Jobrate = 10 on 1-Dec-2002 & later revised to Jobrate =20 effective 1-Jan-2003, then for a particular workdate 16-Dec-02 ,
the report should display one record with Temp_rate= 10 instadof two records with diffenrent rates, other data being same

select EMPLOYEE.EMP_ID,
Job.Job_name Temp_job,
Job_Rate.Jobrate_Rate Temp_Rate,
To_Char(Work_Detail.Wrkd_Work_Date,'MM-DD-YYYY') WorkDate ,
To_Char(Work_Detail.wrkd_Start_Time,'HH24:MI') BeginTime ,
To_Char(Work_Detail.wrkd_End_time,'HH24:MI') EndTime
from Employee , Job, Job_Rate ,Work_Detail,Work_Summary
where EMPLOYEE.EMP_ID = WORK_SUMMARY.EMP_ID
AND WORK_SUMMARY.WRKS_ID = WORK_DETAIL.WRKS_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND JOB.JOB_ID = Job_Rate.Job_IdOriginally posted by ritz1975
I am using a query to get data about temp job & temp rates for an employee database. Problem is this query pulls up two records with different rates for the same work period. THE JOB_RATE table has two different job rates with different JOBRATE_EFFECTIVE_RATE . What candition should I add in this query so that it pulls up the Jobrate applicable to that particular WORKDATE & not all JOBRATES .

i.e,
say if Jobrate = 10 on 1-Dec-2002 & later revised to Jobrate =20 effective 1-Jan-2003, then for a particular workdate 16-Dec-02 ,
the report should display one record with Temp_rate= 10 instadof two records with diffenrent rates, other data being same

select EMPLOYEE.EMP_ID,
Job.Job_name Temp_job,
Job_Rate.Jobrate_Rate Temp_Rate,
To_Char(Work_Detail.Wrkd_Work_Date,'MM-DD-YYYY') WorkDate ,
To_Char(Work_Detail.wrkd_Start_Time,'HH24:MI') BeginTime ,
To_Char(Work_Detail.wrkd_End_time,'HH24:MI') EndTime
from Employee , Job, Job_Rate ,Work_Detail,Work_Summary
where EMPLOYEE.EMP_ID = WORK_SUMMARY.EMP_ID
AND WORK_SUMMARY.WRKS_ID = WORK_DETAIL.WRKS_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND JOB.JOB_ID = Job_Rate.Job_Id
You need to say:

AND job_rate.effective_date =
( SELECT MAX(jr.effective_date)
FROM job_rate jr
WHERE jr.effective_date <= Work_Detail.Wrkd_Work_Date
AND jr.job_id = job.job_id)

It is common to have a job_rate.end_date column to overcome this, so that the condition is simply:

AND Work_Detail.Wrkd_Work_Date BETWEEN job_rate.effective_date AND job_rate.end_date

This simplifies the query, but adds complication to the rate maintenance functionality.|||Thanks Andrewst .

The query has worked & I am satisfied after testing it .Thanks a lot for the help.