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 |
|
hybridGik
Starting Member
26 Posts |
 Posted - 2006-07-11 : 19:42:35
|
| HiI have a query like "select into ##tmptable" in a string and I'm executing it as exec(strSQL)are there any substitutes for the global temp table so that the whole Stored Procedure will be faster than what I have right now.local temp table will not work because i need the query to be used to another query and the local temp table's data is gone everytime i ran the string SQL "exec(strSQL)". thanks. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-11 : 19:57:40
|
| Using global temp tables is a very bad practice.Instead of using a global temp table, create a regular temp table with a create table statement and then insert into that table inside the exec.CODO ERGO SUM |
 |
|
|
hybridGik
Starting Member
26 Posts |
Posted - 2006-07-11 : 20:29:32
|
| ok thanks.and then just drop the created table?can i use select into instead of insert into?thanks |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-11 : 21:06:48
|
| You must use insert when the table already exists.You can drop the table, but it will go away when the procedure that created it finishes.CODO ERGO SUM |
|
|
|
hybridGik
Starting Member
26 Posts |
Posted - 2006-07-12 : 00:51:25
|
| ok thanks for the tips.i will use that instead of global temp table.thanks |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-12 : 02:09:33
|
| >>can i use select into instead of insert into?Create temp table with similar structure of original table and then do Insert inoMadhivananFailing to plan is Planning to fail |
|
|
|
hybridGik
Starting Member
26 Posts |
Posted - 2006-07-12 : 11:36:52
|
| if i use temp table (#) the data from the query string is gone. it won't retain. that's why i've used (##), but it is slow. and if i use a regular table, still i have to drop it even if i'm done running the stored procedure. thanks. |
|
|
|
|
|
|
Global Temporary Table