Sql-server – Move data from one table to other in batch’s

bulkcopyinsertsql-server-2005

I have a table 18 Million records I need to add append it to another table (with the same structure) I tried the import-export wizard, the problem is that the log gets very very big and eventually it took down the full server, I had to kill the process in middle.

I think if I'll do it in batches it'll be faster, but I don't know how to do it.

(I don't need the old table, a record that has been copied could be deleted)

EDIT: bulk insert block all operations, is there a way to prevent him from blocking everyone here?
enter image description here

Best Answer

while (1=1)
begin
    delete top(10000) from oldtable
       output deleted.columnA, deleted.columnB, deleted.columnC
       into newtable (columnA, columnB, columnC)
    if @@ROW_COUNT = 0
       break;
end

Related Solutions

Sql-server – BULK INSERT – best usage

It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like?

Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx

Sql-server – How to move data from Primary Data File to other data file without destroying replication

SSMS is creating a copy of the table, populating it, dropping the original. hence the error.

I'd create a new filegroup, rather then adding files to the primary filegroup because of how the files are used within a file group

You can move a table "in-situ" to a new filegroup by rebuilding the clustered index using CREATE INDEX .. DROP_EXISTING

CREATE NONCLUSTERED INDEX PK_Mytable
    ON dbo.Mytable(PKColumn)
    WITH (DROP_EXISTING = ON)
    ON NewFileGroup

Best Answer

Related Solutions

Sql-server – BULK INSERT – best usage

Sql-server – How to move data from Primary Data File to other data file without destroying replication

Related Question