0

I need to insert records into a new table from an existing table. I used the following query to do this:

Insert into Newtable
Select * from Oldtable where date1 = @date

This query works most of the time, but in one scenario I get 10 million records to be inserted for the date1 value. In this case I'm getting the following error message:

Error : The transaction log for database "tempDB" is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Should I break the query into parts and insert them sequentially, or is there a way to do this with the current query?

SeanC
  • 15,695
  • 5
  • 45
  • 66
Saravanan
  • 11,372
  • 43
  • 143
  • 213
  • 1
    First google hit: [transaction log for database tempdb is full](http://sqlserverdb.blogspot.com/2011/01/transaction-log-for-database-tempdb-is.html) – Mahmoud Gamal Jun 28 '12 at 15:34
  • 1
    Also don't use SELECT * and do specify fields in the destination table. E.g `Insert into Newtable (NewField1, NewField1, NewField3) Select OldField1, OldField2, OldField2 from Oldtable where date1 = @date` – Yuriy Galanter Jun 28 '12 at 17:45
  • http://stackoverflow.com/questions/11230225/how-to-efficiently-delete-rows-while-not-using-truncate-table-in-a-500-000-rows/11230373#comment14753797_11230373 – HLGEM Jun 28 '12 at 21:34
  • The link I posted isn't an exact match for your problem, but the solutions in there may help you. – HLGEM Jun 28 '12 at 21:35

1 Answers1

1

This is, perhaps, a distasteful suggestion. But, you can try exporting the data to a file and then inserting using bulk-insert, with database logging set to SIMPLE or BULK-LOGGED.

More information is at http://msdn.microsoft.com/en-us/library/ms190422.aspx.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786