SQL Server 2008: insert into table in batches

I have a linked server (Sybase) set up in SQL Server from which I need to draw data. The Sybase server sits on the other side of the world, and connectivity is pretty shoddy. I would like to insert data into one of the SQL Server tables in manageable batches (e.g. 1000 records at 开发者_开发问答a time). I.e I want to do;

INSERT IN [SQLServerTable] ([field])
SELECT [field] from [LinkedServer].[DbName].[dbo].[SybaseTable]

but I want to fetch 1000 records at a time and insert them.

Thanks

Karl

I typically use python with the pyodbc module to perform batches like this against a SQL server. Take a look and see if it is an option, if so I can provide you an example.

You will need to modify a lot of this code to fit your particular situation, however you should be able to follow the logic. You can comment out the cnxn.commit() line to rollback the transactions until you get everything working.

import pyodbc

#This is an MS SQL2008 connection string 
conn='DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=DBNAME;UID=USERNAME;PWD=PWD'

cnxn=pyodbc.connect(conn)
cursor=cnxn.cursor()

rowCount=cursor.execute('SELECT Count(*) from RemoteTable').fetchone()[0]

cnxn.close()

count=0
lastID=0


while count<rowCount:
    #You may want to close the previous connection and start a new one in this loop.  Otherwise
    #the connection will be open the entire time defeating the purpose of performing the transactions in batches.

    cnxn=pyodbc.connect(conn)
    cursor=cnxn.cursor()

    rows=cursor.execute('SELECT TOP 1000 ID, Field1, Field2 FROM INC WHERE ((ID > %s)) ' % (lastID)).fetchall()

    for row in rows:
        cursor.execute('INSERT INTO LOCALTABLE (FIELD1, FIELD2) VALUES (%s, %s)' % (row.Field1, row.Field2))   


    cnxn.commit()
    cnxn.close()

    #The [0] assumes the id is the first field in the select statement.
    lastID=rows[len(rows)-1][0]
    count+=len(rows)

    #Pause after each insert to see if the user wants to continue.
    raw_input("%s down, %s to go!  Press enter to continue." % (count, rowCount-count))