批量插入自增列数据

 

批量入库sql 操作：

DECLARE @id INT
SET @id=0;
DECLARE @num INT
SET @num=0;
--set statistics IO off
--SET STATISTICS TIME off 

--SET  IDENTITY_INSERT step ON 
--SET  IDENTITY_INSERT step OFF 

SELECT @id=MAX(id) FROM dbo.step(NOLOCK) 
PRINT('begin==')
WHILE 1=1
BEGIN

--PRINT(@id)
SET  IDENTITY_INSERT step ON  
INSERT INTO step([id],[createtime])
SELECT TOP 100 * FROM dbo.step_all(NOLOCK) 
WHERE id>@id
AND id<=11233766
ORDER BY id
SET @id=@@IDENTITY
PRINT(@id)
SET  IDENTITY_INSERT step OFF 


SET @num=@num+1;
PRINT(@num);
IF(@id>=11233766)
BEGIN
PRINT(@id)
PRINT('end==')
BREAK;
END

END

PRINT('======')

----10870243
--10874644
--SELECT  MAX(id),MAX(createtime) FROM dbo.step(NOLOCK) 

 

 sp

CREATE PROCEDURE [dbo].[sp_tongbu]
AS
BEGIN
    SET NOCOUNT ON;

    declare @i int
    declare @maxid int
    declare @time datetime
    declare @mintime varchar(64)
    declare @maxtime varchar(64)

    set @maxid=0
    /******************* 同步 [t_direct_price_all]**********************/
    select @i=cast(svalue as int) from [t_config] where code='tongbu01'
    select @maxid = max(id) from (select top 10000 id from [t_direct_price_all] where id>@i order by id asc) tmp

    if @maxid>@i
    begin
        insert into [t_direct_price]([id],[isbj])
        select [id],(case when sadtprice+sadttax<>padtprice+padttax then 'Y' else 'N' END) as idbj from [t_direct_price_all] where id>@i and id<=@maxid
        UPDATE [t_config] SET svalue=@maxid,createtime=GETDATE() WHERE code='tongbu01'
    END

    set query_governor_cost_limit 0 
    set @maxid=0
    set @i=0
     
    /******************* 删除 [t_daycount]  15天前数据 **********************/
    SET @mintime=CONVERT(VARCHAR(100), DATEADD(DAY,-15,GETDATE()), 23)
    DELETE [t_daycount] FROM [t_daycount] aa JOIN (SELECT TOP 2000 id FROM [t_daycount] WITH(NOLOCK) WHERE createdate=@mintime ORDER BY id ASC) bb ON aa.id=bb.id

END 

GO

 

 

后台页面操作（推荐，比较高效，配合索引查询到内存入库）：

 public static void NBulkCopy(string connString, string tableName, DataTable dt, int BatchSize)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction))
                    {
                        bulkCopy.BatchSize = BatchSize;
                        bulkCopy.BulkCopyTimeout = 60;
                        bulkCopy.DestinationTableName = tableName;
                        try
                        {
                            foreach (DataColumn col in dt.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            bulkCopy.WriteToServer(dt);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            bulkCopy.Close();
                            transaction.Dispose();
                            conn.Dispose();
                        }
                    }
                }
            }
        }

 

 锁单

update t_order_deposit set [lockname]='052bbd35-b4ba-41e3-88dc-1b638e2cb7d0',locktime=getdate() from t_order_deposit aa join (select top 1 id from t_order_deposit WHERE state='N' and lockname is null order by locktime asc) bb on aa.id=bb.id;
SELECT id,orderid,orderno,orderamount from t_order_deposit where lockname='052bbd35-b4ba-41e3-88dc-1b638e2cb7d0'