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 |
|
liangtp@hotmail.com
Starting Member
27 Posts |
 Posted - 2008-08-09 : 00:33:35
|
| Hi,Need your advice. I need to create a temp table #temp.As I know there are 2 ways:1. Define the temp table with "CREATE TABLE #temp ...", then use "INSERT INTO #temp ..."2 Directly execute "SELECT * INTO #temp FROM ..."I would prefer to choose method 2 as my codes can dynamically returns different number of fields. I wish to know whether there is any downside of choosing this method.Thanks. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-09 : 01:01:55
|
| I believe someone did test, method 1 is faster when move many rows. |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-09 : 01:02:06
|
| Way back under SQL Server 6.5 this was a big no-no, as SELECT INTO was an unlogged transaction so you had to do an immediate backup in order to ensure recoverability.In current versions of SQL Server this is not an issue, but it is still sloppy coding in my personal opinion. What is the business requirement that needs you to output different datasets from the same code?e4 d5 xd5 Nf6 |
|
|
|
liangtp@hotmail.com
Starting Member
27 Posts |
Posted - 2008-08-09 : 01:40:27
|
| Thanks for your valuable advice. I'll go for method 1 then.Blindman, to answer your question my customer table was designed in a way that the fieldnames are "Field1", "Field2", "Field3", etc. These fields definitions are stored in another parameter table, where "Field1" is mapped to "Name", "Field2" ->"SocialSecurityNo", "Field3" -> "Sex", etc. Don't curse me for the bad design. I am only the programmer. I guess the reason why it was designed such was to allow the development work to be done without anticipating what the customers want as their customer table fields.That's the reason why I would use "SELECT * INTO #temp". Otherwise I would have to define the fields using the names in the parameter table. (which is not difficult acually).Thanks. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-08-09 : 20:38:07
|
| The reason it was designed such is because the former DBA was sophomoric. He knew just enough to be dangerous.Boycott Beijing Olympics 2008 |
|
|
|
|
|
|
|
|
temporary table: Define first or SELECT INTO