前言:线上一数据表数据量很大，导致扫描缓慢，新增一数据表转移，转移表中有自增列....
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,布布扣,bubuko.com
原文：http://kinwar.blog.51cto.com/3723399/1384394