INSERT Statement Failed

Hi,

I am trying to perform an insert statement to the database i.e., am coping over 1 billion records from an sql view to a table.
The server has sufficient space on the drives.. However, when I run the INSERT statement it terminates after some time and gives the following error:

Location: qxcntxt.cpp:956
Expression: !"No exceptions should be raised by this code"
SPID: 56
Process ID: 1676
Msg 802, Level 17, State 20, Line 1
There is insufficient memory available in the buffer pool.

Any help please...

Many thanks

Insert in small batches rather than all one billion in a single statement. If your database is in FULL or BULK-LOGGED recovery mode, do frequent log backups while you are inserting batches of data. If your database is in SIMPLE recovery mode, insert CHECKPOINT statements between the batches of insert.

Nicee JamesK --

How can I do that if my INSERT statement is;

 INSERT INTO dbo.EmisEvent (EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal)
 SELECT EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal 
 FROM dbo.EmisEventExtraction WITH (tablock)

N/B: Recovery mode - SIMPLE

Many thanks

Plan A - Import in batches

DECLARE @intMinPatientID INT = 0, @intMaxPatientID INT = 0, @intRowCount = 1 -- 1=Force first iteration
	, @intBatchSize int = 100000	-- Iteration batch size (set to a size that works OK!)
--
WHILE @intRowCount > 0
BEGIN
--
SELECT TOP @intBatchSize
	@intMaxPatientID = PatientID
FROM	dbo.EmisEventExtraction
WHERE	PatientID > @intMinPatientID	-- See note #2
ORDER BY PatientID
SELECT	@intRowCount = @@ROWCOUNT	-- Number of rows found
--
INSERT INTO dbo.EmisEvent (EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal)
 SELECT EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal 
 FROM dbo.EmisEventExtraction WITH (tablock)
--
WHERE	PatientID > @intMinPatientID AND PatientID <= @intMaxPatientID
ORDER BY PatientID	-- See note #1
--
SELECT	@intMinPatientID = @intMaxPatientID
--
CHECKPOINT
--
END

1 It would be best that the inserts are in Clustered Index Order

2 Choose a column with high selectivity. If it is NOT unique some batches will be larger than others. If it is NOT the Clustered Index key then insert order will not match clustered index, and that may create a lot of work maintaining the clustered index as inserts are made.

Plan B:

Export the whole VIEW to an external file, using BCP (Native mode, not CSV), and using a query pre-sorted into the clustered index order (of the TARGET table, NOT the Source table)

Import the file into the new table, and provide the HINT to indicate that the file is already ordered on Clustered Index

I expect that Plan B will be faster ...

Plan C: Use SSIS with an OLEDB Destination setting the batch/commit sizes to an appropriate value. Use an OLEDB Source with a query appropriately ordered by the clustered key column(s) of the destination table as the source.

Make sure SSIS is being run on the server and not on your local client - as that will require sending the data to your client and from your client back to the server. Even with that it will be much quicker than Kristen's Plan A - and only a bit longer than her Plan B.

They probably exist somewhere, but I'd be interested to see a real-world speed comparison, both carefully optimised and a lesser but "well written" alternative, to know which route is the most likely to be productive - no sense spending days optimising something that is either a one-off or where the gain of that careful effort is minimal. Personally I don't use SSIS so, for me!!, the learning time to get involved in that would probably not be productive.

Kristen;

Is this Ok -

DECLARE @intMinPatientID INT = 0, @intMaxPatientID INT = 0, @intRowCount INT = 1
	, @intBatchSize INT = 1000000
--
WHILE @intRowCount > 0
BEGIN
--
SELECT TOP (@intBatchSize)
	@intMaxPatientID = PatientID
FROM	dbo.EmisEventExtraction
WHERE	PatientID > @intMinPatientID	-- See note #2
ORDER BY PatientID
SELECT	@intRowCount = @@ROWCOUNT	-- Number of rows found
--
INSERT INTO dbo.EmisEvent (EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal)
 SELECT EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal 
 FROM dbo.EmisEventExtraction WITH (tablock)
--
WHERE	PatientID > @intMinPatientID AND PatientID <= @intMaxPatientID
ORDER BY PatientID	-- See note #1
--
SELECT	@intMinPatientID = @intMaxPatientID
--
CHECKPOINT
--
END

Many thanks

Looks OK to me.

Sorry about the syntax errors, code was untested of course ... and there might be syntax usage that is not backwards compatible with earlier versions.

However, two thoughts occur to me.

  1. If you have to ask how to fix those errors my worry is that you might not be a safe pair of hands to be sure that this code is working properly for you, has no edge-conditions (duplicates / missing rows in the INSERT) and so on

  2. OTOH we all had to start somewhere and learn our craft ...

So I suppose, in conclusion, after you've got it working my question would be "Do you properly understand how it works and what everything does?" If not then I would suggest that it might not be safe to use and you should perhaps find a better way (such as export-to-file and import-from-file)