Use [db_LOG]
GO
/****** object:storedprocedure [dbo]. [Movefightchars] Script DATE:2015/5/4 16:04:59 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo]. [Movefightchars]
As
Begin
DECLARE @month int
DECLARE @Id int
Set @month =1
Select top 1 @Id =id from Db_kkxy. [dbo]. Fightchars where Addtime < DATEADD (Mm,datediff (Mm,0,dateadd (Month,[email protected],getdate ())), 0) Order by Addtime
while (@Id >0)
Begin
-----Create a Memory table
DECLARE @T Table (
[ID] [bigint] not NULL,
[GameID] [bigint] Not NULL,
[UserID] [INT] Not NULL,
[Charid] [bigint] Not NULL,
[Charlevel] [INT] Null
[Chardbid] [INT] Null
[Usedorder] [INT] Null
[Usedtime] [DateTime] Null
[Usedpill] [INT] Null
[Isbetter] [INT] Null
[Iswin] [INT] Null
[Isused] [INT] Null
[Curattack] [INT] Null
[Curpower] [INT] Null
[Curdamage] [INT] Null
[Addtime] [DateTime] Null
)
-----Inserting qualifying data into the memory table
INSERT INTO @t (
[ID]
, [GameID]
, [UserID]
, [Charid]
, [Charlevel]
, [Chardbid]
, [Usedorder]
, [Usedtime]
, [Usedpill]
, [Isbetter]
, [Iswin]
, [isused]
, [Curattack]
, [Curpower]
, [Curdamage]
, [Addtime]
)
Select Top 1000
[ID]
, [GameID]
, [UserID]
, [Charid]
, [Charlevel]
, [Chardbid]
, [Usedorder]
, [Usedtime]
, [Usedpill]
, [Isbetter]
, [Iswin]
, [isused]
, [Curattack]
, [Curpower]
, [Curdamage]
, [Addtime] from Db_kkxy. [dbo]. Fightchars where Addtime < DATEADD (Mm,datediff (Mm,0,dateadd (Month,[email protected],getdate ())), 0) Order by Addtime
DECLARE @tId int
-------Memory table Max ID
Select top 1 @tId =id from @t ORDER BY Id Desc
--------inserting data from the memory table into the log table
insert into [db_LOG]. [dbo]. [Fightchars] (
[ID]
, [GameID]
, [UserID]
, [Charid]
, [Charlevel]
, [Chardbid]
, [Usedorder]
, [Usedtime]
, [Usedpill]
, [Isbetter]
, [Iswin]
, [isused]
, [Curattack]
, [Curpower]
, [Curdamage]
, [Addtime]
)
Select
[ID]
, [GameID]
, [UserID]
, [Charid]
, [Charlevel]
, [Chardbid]
, [Usedorder]
, [Usedtime]
, [Usedpill]
, [Isbetter]
, [Iswin]
, [isused]
, [Curattack]
, [Curpower]
, [Curdamage]
, [Addtime]from @t
------Delete Memory table data and raw table data
Delete @t
Delete Db_kkxy. [dbo]. Fightchars where Id <= @tId;
----Sleep for 0.1 seconds
WAITFOR DELAY ' 00:00:00.100 '
DECLARE @MaxId int
Set @MaxId =0
Select top 1 @MaxId =id from Db_kkxy. [dbo]. Fightchars where Addtime < DATEADD (Mm,datediff (Mm,0,dateadd (Month,[email protected],getdate ())), 0) Order by Addtime
Print @tId
Print @MaxId
if (@MaxId = @tId or @MaxId =0)
Return
End
End
GO
Data transfer Stored procedures