SQLServer数据迁移自增列IDENTITY

前言:线上一数据表数据量很大，导致扫描缓慢，新增一数据表转移，转移表中有自增列....

USE [master]

GO

--新增一数据库历史区域

CREATE DATABASE [Sfis_History] ON ?PRIMARY

( NAME = N'Sfis_History',FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASfis_History.mdf',SIZE = 3072KB,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )

LOG ON

( NAME = N'sfis_History_log',FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAsfis_History_1.ldf',SIZE = 1024KB,FILEGROWTH = 10% )

-- 1) 直接插入的方法

SELECT * INTO Sfis_History.dbo.T_matstate FROM Sfis.dbo.T_matstate WHERE scandate < '2013-01-01'

SELECT * INTO Sfis_History.dbo.T_matstatereplace FROM Sfis.dbo.T_matstatereplace WHERE scandate < '2013-01-01'

-- 2)先新增表结果，再转移数据

SELECT * INTO Sfis_History.dbo.T_matstate FROM Sfis.dbo.T_matstate WHERE 1 <> 1

SELECT * INTO Sfis_History.dbo.T_matstatereplace FROM Sfis.dbo.T_matstate WHERE 1 <> 1

--打开手动指定id选项,转移数据时指定具体列,完成后关闭选项

SET IDENTITY_INSERT Sfis_History.dbo.T_matstate ON

SET IDENTITY_INSERT Sfis_History.dbo.T_matstatereplace ON

INSERT INTO dbo.T_matstate (

id

,line

SELECT ?

FROM Sfis.dbo.T_matstate WHERE scandate < '2011-01-01'

SET IDENTITY_INSERT Sfis_History.dbo.T_matstate OFF

SET IDENTITY_INSERT Sfis_History.dbo.T_matstatereplace OFF

--删除线上数据

DELETE FROM Sfis.dbo.T_matstate WHERE scandate < '2013-01-01'

DELETE FROM Sfis.dbo.T_matstatereplace WHERE scandate < '2013-01-01'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!