Insert Into "Error"

[Stickarm]

I have a table of data with about 3 mill records, all are text fields. I want to move them into an empty table with the correct indexes and primary keys. I need to transform some of the data on the insert. I am using the following code.CREATE PROCEDURE sp_editimportdata
AS
insert into [sales and use returns]([main acct],[sub acct] ,
[tax type] ,period ,source ,[transaction date]... [[tax due],remitted )
select disc.col001, disc.col002 , disc.col003 ,
disc.col004 , disc.col005 ,
convert (datetime,disc.col006) ,..
(convert(money,disc.col047)) ,
(convert(money,disc.col048))
from disc

I run this Stored Proc and I keep getting the following error:
Server: Msg 1105, Level 17, State 2, Procedure sp_EditImportData, Line 3
Could not allocate space for object '(SYSTEM table id: -3252013)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

Is there a better way for me to do this or is there a problem with my code.

Phil

 

[RickCole]

What size do you currently have TEMPDB set to??

If it's not that large and you have the free disc space increase the size of TEMPDB.

A good rule of thumb (although not an exact science) is to make TEMPDB either as big as the biggest table in your system or half the size of your biggest database.

Rick.

 

[Tim1]

Hi Phil,

It seems like you are filling tempdb when you try to run your program, probably because you are moving too much in a single transaction (3 million rows is an awful lot).

There are several things that you can do:

1. Batch the program so that it moves a few rows (say 10,000 at a time) and just put a loop in that the program to repeat until all the rows are moved. You will need to come up with some way of working out which rows you have moved.

2. Drive a cursor through the original table, picking the information and copying it into a new table, committing every x rows.

3. BCP the data out of the original table and back into the new table.

4. Speak kindly to your dba and get them to extend the size of tempdb while you complete this.

Hope this gives you some ideas,

Tim.

p.s. code looks fine to me