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.

  • SQL query for getting data in two fields from one column
  • How can I avoid to get data again in the case 'select then update'
  • Use of top over the subquery with order by inside the subquery
  • How to group by the each week upto last six week sundays dates in sql?
  • Can I use SQL Server Management Studio Express to connect to an MSDE database?
  • How to get values of user defined variables from a database?
  • 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.