Drop table problem

In my SP, i will select some data and put into my cursor.
select * in myTempTable_1 from myCustomer
select * in myTempTable_2 from myInvoice.
at the end of the sp ,
i will drop all temptable.
The problem is .. Does this approach is correct ?
If there is several people use this store procedure, 1st people drop the
table, 2dn people start to run that sp, any problem '> select * in myTempTable_1 from myCustomer
This isn't valid syntax. I assume you mean SELECT * INTO ...
If you need to create tables then use a local temp table or table
variable rather than a permanent one. That way the table has local
scope and there is no potential conflict in a multi-user environment.
Many times you can avoid having to create temp objects by restructuring
procedural code as set-based queries. I'm not sure in what context you
would use statements like the example you gave. There seems to be
little point in selecting an entire table into a new one. In any case,
you should avoid using cursors. Cursors are rarely a good idea in SQL
Server.
Hope this helps.
David Portas
SQL Server MVP
--
.|||Could you tell me how to write this SQL sytnax ?
Thanks a lot
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1108304357.347054.43940@.f14g2000cwb.googlegroups.com...
> This isn't valid syntax. I assume you mean SELECT * INTO ...
> If you need to create tables then use a local temp table or table
> variable rather than a permanent one. That way the table has local
> scope and there is no potential conflict in a multi-user environment.
> Many times you can avoid having to create temp objects by restructuring
> procedural code as set-based queries. I'm not sure in what context you
> would use statements like the example you gave. There seems to be
> little point in selecting an entire table into a new one. In any case,
> you should avoid using cursors. Cursors are rarely a good idea in SQL
> Server.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
> .
>|||Here is an example:
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
that allows you to create a looping logic with T-SQL
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:uV4tgKlEFHA.2572@.tk2msftngp13.phx.gbl...
> Could you tell me how to write this SQL sytnax ?
> Thanks a lot
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> '?:1108304357.347054.43940@.f14g2000cwb.googlegroups.com...
>|||BY USING SET ROWCOUNT ' I CAN MAKE THE TEMPTABLE RUN IN LOCAL MACHINE ?
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> glsD:cupg3k$41l$1@.news01.intel.com
..
> Here is an example:
> http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
> that allows you to create a looping logic with T-SQL
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Agnes" <agnes@.dynamictech.com.hk> wrote in message
> news:uV4tgKlEFHA.2572@.tk2msftngp13.phx.gbl...
>|||I'm not sure that Vinod's example is relevant to what you are trying to
do - mainly because you haven't stated explicitly what you ARE trying
to do. It think Vinod was responding to your mention of cursors in your
original post while I was addressing the temp table question.
In answer to the additional questions you have asked: Here's an example
of a SELECT INTO statement:
SELECT col1, col2
INTO NewTable
FROM YourTable
this creates a new table and populates it with the data from
"YourTable".
Secondly, the question of putting a temptable on the "local machine".
Usually your goal should be to avoid processing data on your local
machine. This is a SQL Server programming newsgroup and SQL code runs
on the server and then returns results to the client for display
printing or further processing. You should consult a resource for your
development language or client application for more information on how
to process data at the client.
If you need more help with SQL Server, please consult the following
article which explains the best way to describe your problem for the
group:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. It is very hard
to debug a procedure without seeing it.
And, no, that approach is horrible. In SQL, the goals are:
1) Do not use cursors
2) Do use temp tables
3) Do not use SELECT * in production code.
4) Try to do everything in one statement.
Based on absolutely nothing you told us, I will guess that you want to
do a JOIN between Customers and their invoices.