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 |
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2012-12-05 : 02:36:56
|
I have understood that in SQL Server it is possible to create temp tables. Why would I do so and not just create a database named something like "temptabledatabase" and create temp tables to there just to be dropped seconds later? If I go with those actual temp tables (not create and drop -system), what differences do I need to make to my code of "select into - select * - drop table"? |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-05 : 05:06:52
|
If you create a table it is visible to all spids.Say you have an SP which can be called by many users - that couldn't create a permanent table as the next instance would get an error.Also if a process creates a table then fails the table would be left there - the next run would get an error as it couldn't create the table.With temp tables you do not need to code drop statements (but you can). They will be created in tempdb - there can be issues if system databases have a different collation to the user databases so try to make sure that doesn't happen.Also look at table variables for small amounts of data.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2012-12-05 : 05:26:37
|
What is SP in this context? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-05 : 05:28:22
|
quote: Originally posted by KilpAr What is SP in this context?
Stored Procedure (SP)--Chandu |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2012-12-05 : 05:31:02
|
Also, since I'm creating the "custom kind of temp tables" with SELECT INTO, can I SELECT INTO to temp tables? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-05 : 06:07:10
|
Yes.Try it and see.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2012-12-05 : 08:04:30
|
Thanks, I will try this as an optimization. |
|
|
|
|
|