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.

  • MySQL REGEXP to SQL Server
  • SQL Server Stored Procedure - Executing different queries by CASE
  • Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'
  • Entity Framework sending parameters as unicode causing performance problems
  • Error running MVC4 on Azure
  • Getting count of similar columns (at least 1 column same in the rows)
  • if statements in sql query
  • 7 Solutions collect form web for “insert data in increments”

    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.

    http://msdn.microsoft.com/en-us/library/aa174646(SQL.80).aspx 
    

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.