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 |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2008-03-12 : 07:11:49
|
| Created my tableWhy can't I do a SELECT * INTO #validSOR FROM ...which I did once, and all was fineNow when I do it again I get this error:Server: Msg 2714, Level 16, State 6, Line 1There is already an object named '#validSOR' in the database.I was wondering for one thing - will it append or overwrite? Not sure if there's an option I can give in the statement, but that doesn't matter - I can truncate it beforehand. Which is what I did anyway. But that doesn't seem to matter. Surely it's GOOD if the object exists! I'm trying to insert into it, not recreate it. I really am. There is no CREATE in the statement and the error makes it sound like that. Do you recognise this error?I tried just inserting. Then I tried truncate table #validSOR, then doing an insert and I got the above error...Is there something sick about temp tables? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-12 : 07:15:28
|
SELECT * INTO...syntax creates new table and populates it with records from given table. If you don't want to re-create table, you should use regular INSERT statementINSERT #TempSelect col1, col2,... from SomeTable Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-03-12 : 07:30:15
|
| Got it. Brilliant.It's all syntax. I hate it. I thought it was INSERT INTODidn't work so got into the weird form of SELECT * INTO <table> FROM.... and nobody told me that creates the new objectTHIS is why I come to SQL TEAM Forums - because it's interactive! Looking stuff up online is static and ends in tears!!The old form as you said is good, but that weird form is excellent because it means I can drop the table afterward and leave it to auto recreate when it starts. Neat.Did you see my previous post today? Could really do with knowing if I should use CURSORS or not, and how to set up a FOR loop...? A web reference..?Thanks very much, Harsh |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-12 : 11:56:57
|
You can also check for the exist of the temp table to know how to deal with it: creat a new one, truncate an existing one, etc..IF OBJECT_ID('tempdb..#validSOR') IS NOT NULL DROP TABLE #validSOR |
|
|
|
|
|
|
|
|
Can't populate #table...!