insert data in increments
Does anyone know how to insert data into a table in increments of about 1000? I have a table with thousands of records that I want to insert into an identical table on a different server.
7 Solutions collect form web for “insert data in increments”
you need to look into sqlbulkcopy.batchsize attribute
Depending on the version, you could use SSIS(2005/2008) or DTS (2000/7) or if you are comfortable with the command line then BCP or if you are an admin and this is a one-time shot AND depending on the version again, Enterprise Manager has query interfaces that will certainly allow you to write SQL (INSERT INTO…) or SELECT * FROM … but not knowing the version or the purpose its difficult to be specific..
SSIS and most bulk copy tools (including bcp) will run in a mode that allows batched inserts. Given that you are moving it across to another server you will probably want to use a bulk load tool to do this anyway.
If table has a Primary key
Declare @Start Integer Set @Start = 1 Decalre @End Integer Set @End = 1000 Declare @PKs table (rowNo Integer identity Primary Key Not Null, PK Integer Not Null) Insert @Pks(PK) Select PrimaryKeyColumn Form SourceTable Where [Criteria to select rows you want] While Exists(Select * From @PKs) Begin Begin Transaction Insert DestTable([ColumnList]) Select [ColumnList] From SourceTable S Join @Pks p On p.PK = s.PrimaryKeyColumn Where p.rowNo Between @Start and @End Commit Transaction -- ---------------------------------- Delete @PKs where rowNo Between @Start and @End Set @Start = @End + 1 Set @End = @Start + 999 End
SELECT … LIMIT n OFFSET m; is the standard way of extracting huge amounts of data in small chunks. But reading your question, it seems that you are trying to replicate data between two database servers. Maybe you should check the if the DB engine has replication support.
You want to move the data from one table to another? Try BCP utility.
It works great with huge amount of data.
I asked a similar question and the Import/Export Wizard suggested worked well for me. It was very quick (a few seconds) to insert thousands of records from one database on one machine to another on a different machine.