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 |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2007-10-31 : 17:36:05
|
| Is there a way to insert or update values into a temp table with no identity? I have a variable table with an identity field, and I must delete the table's data at each iteration point in a while loop. The problem is the only way to delete the identity field is to destroy the table. But if I create and delete a temp table many, many times over, I imagine it might cause considerable overhead, but I'm not sure. The problem is without an identity field, I don't know how to insert or update its values.Two questions then: How much overhead would creating and destroying a variable table, that will have a maximum of 4 rows at any given point, many, many times over inflict? And how does one update or insert values into a variable table without an identity field?Thank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-31 : 17:42:17
|
| I don't understand what problem you are having. Inserting and updating is rather simple. What does an identity have to do with your problem?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-31 : 18:02:03
|
| truncate the table instead of deleting the data. trucate also resets the identity value._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-01 : 02:54:20
|
| If you use temp table you can truncate it and you cant truncate a table variableMadhivananFailing to plan is Planning to fail |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2007-11-01 : 10:32:52
|
Interesting, Truncate doesn't work on table variables. Unfortunately, I'm using a table variable at the moment. I apologize for some bad wording in my first post. Please allow me to clarify the identity issue. Say I create a table variable where I insert 3 records as part of a query statement:TableVar----------------------------------------ID Column1 Column21 ABC DEF2 GHI JKL3 MNO PQR As part of a while loop, calculations will be performed on this set of data. Then, I delete all the data in the table, at the conclusion of an iteration in the while loop. The next iteration will execute the query and rinse and repeat until the loop ends. The problem is, in the second iteration for example, TableVar will resemble something like the following:TableVar----------------------------------------ID Column1 Column21 2 3 4 STU VWX5 YZ0 1236 456 789 If I'm correct then, the delete command will not reset the ID field. Destroying and creating the table for each iteration may cause some overhead, but one can't destroy a table variable. And creating a temp table manufactures I/O access, which may also cause more overhead, but the truncate command would work and I could destroy and create it as I please; so, maybe that's my best bet. What about employing SET IDENTITY_INSERT {Table_Name} ON? Can I use it with temp variables? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-01 : 12:55:16
|
| You keep asking questions about loops and we keep telling you that you probably don't need to do it this way. How about posting a new thread with a high-level example of what you are trying to do and then seeing if we can solve via a set-based method instead?As for your last post, why don't you try running that command against a table variable? Also, in your example, why do you need to reset the identity value, why can't your process just keep track of where it is?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
how to insert into a temp table with no identity?