SQL Server数据库迁移偏方

发表于：2011-5-03 09:38

作者：听风吹雨来源：51Testing软件测试网采编

　　--2 链接服务器(省略)

　　--3 存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Viajar>
-- Create date: <2011.04.14>
-- Description: <转移数据>
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetMoveData]
AS
BEGIN
DECLARE @Id1 INT,@Id2 INT
DECLARE @MaxId INT--原表的最大值
SET @Id1 = 0
SET @Id2 = 0
SELECT TOP 1 @Id1 = Id FROM Temp_MoveManage WHERE IsDone = 0 ORDER BY Id
SELECT TOP 1 @Id2 = Id FROM Temp_MoveManage WHERE IsDone = 0 AND Id > @Id1 ORDER BY Id
SELECT @MaxId = MAX(Id) FROM [dbo].[ClassifyResult]
IF(@Id1 != 0 AND @Id2 != 0 AND @MaxId>=@Id2)
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = '
SET IDENTITY_INSERT [ClassifyResult_T] ON
INSERT INTO [dbo].[ClassifyResult_T](
[Id]
,[ClassId]
,[ArchiveId])
SELECT
[Id]
,[ClassId]
,[ArchiveId]
FROM [dbo].[ClassifyResult]
WHERE Id >= '+ CONVERT(VARCHAR(100),@Id1) + ' and Id < '+ CONVERT(VARCHAR(100),@Id2) + '
ORDER BY Id
SET IDENTITY_INSERT [ClassifyResult_T] OFF '
EXEC (@sql)
UPDATE Temp_MoveManage SET IsDone = 1 WHERE Id = @Id1
END
END

32/3<123 >

测试技术了解

每月专享直播

挣点稿费

走进神秘的银行测试

文章资料精选