SQLServer分页查询方法整理以及批量插入操作SqlBulkCopy

时间:2024-08-27 23:07:14

分页查询

通用方法:sqlserver 2005 +

ROW_NUMBER() OVER()方式:

SELECT TOP 50 * FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY ID) as rowID FROM TripDetail)as b where b.rowID >1010001 ;

TOP NOT IN方式 :

SELECT TOP 50 * FROM TripDetail where ID not in(SELECT TOP 1010001 ID FROM TripDetail ORDER BY ID) ORDER BY ID;

sqlserver 2012 +

offset fetch next方式:

SELECT * FROM TripDetail ORDER BY ID OFFSET 1010001 ROWS FETCH NEXT 50 ROWS ONLY;

经测试,最后一种方式性能最佳,但是需要sqlserver版本高一点,第一和第二个方式性能差不多。

批量插入

//SqlConnection conn; DataTable table

SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = tablename; //表名
bulkCopy.BatchSize = table.Rows.Count; bulkCopy.WriteToServer(table);