Basic insert for large tables

Question

Basic insert for large tables

lmacdonald

Hall of Fame

Points: 3763
More actions
September 22, 2016 at 2:02 pm

#400766

Hi,
This seems simple but I'm having trouble. We have a process that when it falls behind starts to do very large inserts, selecting from one table into another. As they are written now, when the source table gets very large the log grows on the server to the point it maxes the drive. I would like to modify the syntax so it does batches of 10,000 at a time. All my attempts at putting it in a while loop have just lead it to insert the same 10,000 rows each time. Thanks for any help.
INSERT INTO [dbo].[ErrorLog_last48]
([EventDate]
,[ApplicationID]
,[SessionID]
,[ServerName]
,[Level]
,[Message]
,[StackTrace]
,[MetaData]
,[DateIn])
SELECT TOP 10000
[EventDate]
,[ApplicationID]
,[SessionID]
,[ServerName]
,[Level]
,[Message]
,[StackTrace]
,[MetaData]
,[DateIn]
FROM [dbo].[ErrorLogExport]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

You're not specifying a way of telling which records are already in the destination table. Without that, you're going to insert the same records over and over.maybe you could use OFFSET and a windowing function to specify which rows to pull.

lmacdonald Hall of Fame Points: 3763 More actions · Answer 2

I agree. I know what the problem is, that it doesn't know where it left off. I thought there was a way to use the MAX or MIN function and run each iteration on the old MAX or MIN value, + 1, or using the ORDER clause. Not explaining it very well, I don't know how to do the windowing ROWSET thing, but I'll dig into it. Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Here's an example on how to do it.

--Create the sample data

CREATE TABLE Table1( n int);

CREATE TABLE Table2( n int);

WITH

cteTally(n) AS(

SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n

FROM sys.all_columns a, sys.all_columns b

)

INSERT INTO Table1

SELECT n

FROM cteTally

DECLARE @Rows int = 1,

@Counter int = 0;

WHILE @Rows > 0

BEGIN

INSERT INTO Table2

SELECT n

FROM Table1

ORDER BY n OFFSET (@Counter) ROWS FETCH NEXT 10 ROWS ONLY;

SET @Rows = @@ROWCOUNT;

SET @Counter += 10;

END;

--Use if the target table has triggers that might affect @@ROWCOUNT

--DECLARE @nRows int = (SELECT COUNT(*) FROM Table1),

-- @Counter int = 0;

--WHILE @Counter < @nRows

--BEGIN

-- INSERT INTO Table2

-- SELECT n

-- FROM Table1

-- ORDER BY n OFFSET (@Counter) ROWS FETCH NEXT 10 ROWS ONLY

-- SET @Counter += 10;

--END;

SELECT *

FROM Table2

ORDER BY n;

DROP TABLE Table1, Table2;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76996 More actions · Answer 4

The best option here may be the EXCEPT operator.

INSERT INTO [dbo].[ErrorLog_last48]

([EventDate]

,[ApplicationID]

,[SessionID]

,[ServerName]

,[Level]

,[Message]

,[StackTrace]

,[MetaData]

,[DateIn])

SELECT TOP 10000

[EventDate]

,[ApplicationID]

,[SessionID]

,[ServerName]

,[Level]

,[Message]

,[StackTrace]

,[MetaData]

,[DateIn]

FROM [dbo].[ErrorLogExport]

EXCEPT

SELECT

[EventDate]

,[ApplicationID]

,[SessionID]

,[ServerName]

,[Level]

,[Message]

,[StackTrace]

,[MetaData]

,[DateIn]

FROM [dbo].[ErrorLog_last48]

It's not clear which of your columns can be NULL, but EXCEPT will treat NULLs as being the same, which is what you want in this case.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

lmacdonald Hall of Fame Points: 3763 More actions · Answer 5

A 1000 Thanks. I'm beat, will give this a shot tomorrow.