把删除数据备份到数据仓库表

 

数据库中某个表的数据过多,查询的时候比较慢,需要删除一部分过期的数据,但这部分数据以后有可能被用到,所以要把删除的数据备份到数据仓库中;

下面演示:

第一步 创建表tPublicTable

if exists (select * from sysobjects where id = OBJECT_ID('[tPublicTable]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [tPublicTable]

CREATE TABLE [tPublicTable] (
[fID] [int]  IDENTITY (1, 1)  NOT NULL,
[fTypeModel] [nvarchar]  (50) NOT NULL,
[fTypeCode] [nvarchar]  (50) NOT NULL,
[fTypeName] [nvarchar]  (50) NOT NULL,
[fNote] [nvarchar]  (100) NULL)

ALTER TABLE [tPublicTable] WITH NOCHECK ADD  CONSTRAINT [PK_tPublicTable] PRIMARY KEY  NONCLUSTERED ( [fID] )
SET IDENTITY_INSERT [tPublicTable] ON

INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 1,N'Devicetype',N'1001',N'800SH',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 2,N'Devicetype',N'1002',N'801HD',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 3,N'Devicetype',N'1003',N'803HD',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 4,N'Devicetype',N'1004',N'KV806',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 5,N'Devicetype',N'1005',N'800GH',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 6,N'Devicetype',N'1006',N'KB-817',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 7,N'Devicetype',N'1007',N'KV501',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 8,N'Devicetype',N'1008',N'800SDH',N'单机版机型')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 9,N'Devicetype',N'1009',N'网络机型',N'这里是指网络版加歌软件')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 13,N'tstatisticalIP',N'0',N'0',N'0')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 11,N'tStandaloneLoginLogDetail',N'101199986',N'1000000',N'0')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 12,N'tNetLoginLog',N'101199986',N'1000000',N'0')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 14,N'tFTPDownLoadStatistical',N'0',N'1000000',N'0')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 15,N'tSongDownLoadStatistical',N'14212941',N'1000000',N'0')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 19,N'test81',N'0',N'0',N'4')
INSERT [tPublicTable] ([fID],[fTypeModel],[fTypeCode],[fTypeName],[fNote]) VALUES ( 20,N'test82',N'0',N'0',N'0')

SET IDENTITY_INSERT [tPublicTable] OFF

 

 第二步 创建数据仓库表tPublicTableBak  我这里是在同一个数据库

if exists (select * from sysobjects where id = OBJECT_ID('[tPublicTableBak]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [tPublicTableBak]

CREATE TABLE [tPublicTableBak] (
[fID] [int]  IDENTITY (1, 1)  NOT NULL,
[fTypeModel] [nvarchar]  (50) NOT NULL,
[fTypeCode] [nvarchar]  (50) NOT NULL,
[fTypeName] [nvarchar]  (50) NOT NULL,
[fNote] [nvarchar]  (100) NULL)

ALTER TABLE [tPublicTableBak] WITH NOCHECK ADD  CONSTRAINT [PK_tPublicTableBak] PRIMARY KEY  NONCLUSTERED ( [fID] )

 

第三步 删除ID< 5的数据,在实际当中应该是日期用的比较多; 并把删除的数据写入仓库表

DELETE FROM dbo.tPublicTable
OUTPUT 
       Deleted.fTypeModel ,
       Deleted.fTypeCode ,
       Deleted.fTypeName ,
       Deleted.fNote
       INTO tPublicTableBak
WHERE fid <5


SELECT * FROM tPublicTableBak

 其它相关:

-- 查看修改前和修改后的内容,结果自己执行后查看

UPDATE dbo.tPublicTable
SET fTypeCode = 0,fTypeName = 0
OUTPUT Deleted.fTypeCode,Deleted.fTypeName,
Inserted.fTypeCode,Inserted.fTypeName
WHERE fTypeModel = 'tStandaloneLoginLogDetail'