MSSQL Mass Copy

To avoid locking a table for a damn long time, this is how Google told me to do mass copies of data in Microsoft SQL Server:

DECLARE @BatchSize INT = 1000
DECLARE @IdMax INT = 25179272
DECLARE @i INT = 0

WHILE @i <= @IdMax BEGIN
	INSERT INTO [mydata].[dbo].[ProjectEventValues] WITH (TABLOCK)
		([projectId]
		,[descId]
		,[index]
		,[controllerId]
		,[timestamp]
		,[value])
	SELECT
		s.projectId,
		s.descId,
		s.[index],
		s.controllerId,
		s.[timestamp],
		s.value
	FROM [mydata].[dbo].[ProjectEventValues_temp]s
	WHERE s.eventId >= @i AND s.eventId < (@i+@BatchSize)
	SET @i = @i + @BatchSize
	PRINT @i
END
GO

Here for future reference.

..and another one, for personal reference:

USE [mydata]
GO
DECLARE @PID uniqueidentifier
DECLARE @i int = 0

DECLARE cur CURSOR LOCAL FOR
SELECT projectId FROM Projects WHERE deleted IS NULL

OPEN cur

FETCH NEXT FROM CUR INTO @PID

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @i
    PRINT @PID
    SET @i = @i + 1

    INSERT INTO [mydata].[dbo].[ProjectEventValues.new] WITH (TABLOCK)
        ([projectId]
        ,[descId]
        ,[index]
        ,[controllerId]
        ,[timestamp]
        ,[value]
        ,[aggregated])
    SELECT
        s.projectId,
        s.descId,
        s.[index],
        s.controllerId,
        s.[timestamp],
        s.value,
        0
    FROM [mydata].[dbo].[ProjectEventValues]s
    WHERE s.projectId = @PID
    AND s.timestamp >= '2015-03-04'
    AND s.timestamp < '2015-03-05'

    FETCH NEXT FROM CUR INTO @PID
END

Yeh.

Leave a Reply

Your email address will not be published.