SQLServer数据迁移自增列IDENTITY

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

USE [master]

GO

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

CREATE DATABASE [Sfis_History] ON  PRIMARY

( NAME = N‘Sfis_History‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Sfis_History.mdf‘ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N‘sfis_History_log‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\sfis_History_1.ldf‘ , SIZE = 1024KB , FILEGROWTH = 10% )

GO

-- 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

,model

,matstate

,mat

,pingming

,gg

,barcode

,remark

,operator

,oper_date

,scan

,status

,change

,scandate

,scanoperator

,feederno

,gd

,changemat

,machine

,dc

,ln

,feeder)

SELECT  

id

,line

,model

,matstate

,mat

,pingming

,gg

,barcode

,remark

,operator

,oper_date

,scan

,status

,change

,scandate

,scanoperator

,feederno

,gd

,changemat

,machine

,dc

,ln

,feeder

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‘

本文出自 “畅想天空” 博客，请务必保留此出处http://kinwar.blog.51cto.com/3723399/1384394

SQLServer数据迁移自增列IDENTITY,古老的榕树,5-wow.com