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 |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2003-11-25 : 10:53:09
|
| Hello all. Is there a best practice rule in regards to temp tables?I am migrating a large amount of data between two databases. To pull the data over I am using temp tables to hold the data I need while I manipulate it for my destination database.My script is using about 30 temp tables of various size. Is that too many?The reason I ask is because I am getting this weird error. It is bombing on an area where I am setting a temp variable for my inserts. The syntax is correct, so I don't understand what is happening, unless it is related to my computer running out of memory because of the temp tables.ThanksAj |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-25 : 11:10:17
|
| temp tables are held on disk in tempdb so you won't be running out memory.>> It is bombing on an area where I am setting a temp variable for my inserts?Can you post the codedeclare @tbl varchar(10)set @tbl = '#a'select @ from #aThat would work because table names have to be literals.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-11-25 : 15:23:57
|
| This is the area that it does not like.DECLARE @txtMG as varchar(50)SET @txtMG = 'Mid*** ******** ********** Group' (stars are to keep my client confidential)This makes no sense! There is nothing wrong there. I don't think that this is the issue. If I highlight the chunk of code it runs just fine. I think it is something else deeper. I have over 1800 lines of code right now, so I don't want to post it!Aj |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-11-25 : 15:32:03
|
| Betcha its the code just above it....Can you post a small snipet of that?Also can you use the debugger in QA?Brett8-) |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-11-25 : 17:50:42
|
| Just below it.I found it.I fixed it.Now, I get the following message once my code starts to execute:"Warning: The table '#tmptProvider' has been created but its maximum row size (33981) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes."If I am reading this correct, it is saying that as nice as tables are, they will only hold so much data per row. And I have exceeded it (blown it away is more like it).So, do I create four temp tables and break this data up accordingly?If I do that, I planned on making sure that my primary key was a part of each of the four tables, that way I can just inner join them to fire up my cursor.How does that sound?Thanks everybody!Aj |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-25 : 18:12:54
|
| Yep that's the way to do it (could be spread over many rows in the same table as well).except for "that way I can just inner join them to fire up my cursor"If you are creating a temp table why would you need a cursor - you can do row by row processing (if you really must) directly from the temp table.8060 bytes is a restriction on all row sizes for tables in sql server. You can get round it with text columns though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Best practice with temp tables